IBM Cognos Analytics 11.0.11

I need to add a customized field (Period Type) in Table A based on Table B. 
When the Purchase Date is 13/06/2018, it's will auto search Table B for the range, and fill in Period Type in Table A with A01

I try add in new Data Item with expression, but it's not working.

Is anyone know what is the correct way to add field like this?

 

Table A:

PO Number	Purchase Date	Period Type
PO001	13/06/2018
PO002	13/01/2019

Table B:

Period Type	Start	End
A01	01/01/2018	31/01/2018
B01	01/01/2019	31/01/2019

------------------------------
Wendy Tee
------------------------------

#CognosAnalyticswithWatson

Hi Wendy, 
Is the Period Type column not an entry within your calendar dimension? If it were you could simply just pull this field into your query and the relationship between fact and dim should solve that problem. If its not an entry i would certainly recommend adding it to the calendar dimension. 


------------------------------
jonathan chesterton
NHS Supply Chain
------------------------------

Hi Wendy,
The answer depends a bit on your scenario:

1. If you're using Framework Manager models then you can create a relationship with the expression:
   TableA.PurchaseDate between TableB.Start and TableB.End
2. If you're using Data Modules, then you can not create such a relationship, it is being added to an upcoming release. In that case you may want to create a view in the database that picks up the extra column using a join expression similar to what you'd use in FM. You can also use type-in SQL, which is supported in Data Modules from release 11.1.x and up, see: https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_ca_mdlg_sql_table.html
3. If you're using Report Studio then you can use the same technique as for Framework Manager.

------------------------------
// Henk
------------------------------

You need to use a JOIN in the report. Use the "date file" in table A and the "start" and "end" in table B.

Purchase date> = Start AND Purchase date <End

Anyway, I think it will be better to solve that in the ETL process.

------------------------------
Federico Balsa
------------------------------