topic Calculated Dimension Error help Aggr(if(concat(count(distinct( in QlikView

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

Fri, 20 Nov 2015 05:40:06 GMT

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

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 — Fri, 20 Nov 2015 09:20:25 GMT

HI,

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

Kushal_Chawda — Fri, 20 Nov 2015 09:47:44 GMT

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)