Calculated Dimension Error help Aggr(if(concat(count(distinct(

[#1]

Hi,

I have a file that contains more than 60ml records.

It is very hard to change load script. I have following information with me:

Glimpse of load script:

Load *

FROM PREVIOUS_MONTH;

CONCATENATE

LOAD *

FROM CURRENT_MONTH;

Previous=1 (last month)

Current=1 (current month)

Contract_ID

REGFLG_key (Flags - contains only 3 values: NULL, TB and BKB)

I want to make a matrics like below:

If Previous month flag do not match current month flag then only display DISTINCT contract_IDs

Contract_ID	Last_month_flag	Current_month_flag
1	TB	BKB
2	BKB	TB
10001	TB	BKB
36605550	TB	BKB
265444	BKB	TB

A Simple logic in Dimension (PIVOT OR Straight table)  is not working:

NOTHING IS WORKING:

f(Previous=1 ,current month flag)

ALSO TRIED:

Aggr(If(current=1 and (Len(Trim(Concat([REGFLG_key], ','))) > 1, 1, 0), [REGFLG_key]))

CAN YOU PLEASE HELP?

HUGE THANKS

- DEEP

[santhosh_k_n]

HI,

Try using match function in the where class in load script and load only the values which flags are not matching.

BR

SK

[Kushal_Chawda]

try creating the flag in script itself if both the fields are present in same table

if( [Current Month Flag]<> [Previous month Flag],Contact_ID) as Flag_ContractID

Now you can use the expression: Count(distinct ContractID)