topic Re: Help in set analysis in QlikView

Help in set analysis

ali_hijazi — Thu, 04 Sep 2014 19:05:55 GMT

Dears got the following data

LOAD * Inline [

DEFECT_ID, DEV_PLAN_ID, STATUS, DATA_SOURCE

X,1,TESTED,CRM

X,2, CANCELLED, CRM

X,1,NOT TESTED, DW

X,2, CANCELLED, DW

];

I have a pivot table with the two dimensions Defect_id, and Dev_plan_id

but what I want to show is the Status of the CRM data source when the the DW data source of the same defect id and dev plan id has a different status

i.e.

the pivot table will be showing from the above data the following

X, 1, TESTED

I put the following expression

only(AGGR(ONLY({<DATA_SOURCE={'CRM'}, DEFECT_ID={"=COUNT(DISTINCT STATUS)>1"}>} STATUS),DEFECT_ID,DEV_PLAN_ID))

but I got X,1, tested

2, Cancelled

PLEASE ADVISE

Re: Help in set analysis

NickHoff — Thu, 04 Sep 2014 19:13:01 GMT

You wouldn't use set analysis for this expression because there is no aggregation being performed. What you could do is an if statement.

=IF(DEFECT_ID =- DEV_PLAN_ID, STATUS = 'TESTED', 'CANCELED')

Re: Help in set analysis

ali_hijazi — Thu, 04 Sep 2014 19:23:16 GMT

no I can't use if because I have plenty of statuses

this is a sample data that I showed

Re: Help in set analysis

maxgro — Thu, 04 Sep 2014 19:34:01 GMT

maybe

if(max(aggr(count (distinct STATUS), DEFECT_ID, DEV_PLAN_ID))>1,

only({$ <DATA_SOURCE={CRM}>} STATUS)

)

Re: Help in set analysis

NickHoff — Thu, 04 Sep 2014 19:42:39 GMT

I dont' understand your logic behind the status, but If you nest your if statements with the correct logic for the status it should work.

=IF(DEFECT_ID =- DEV_PLAN_ID AND DATA_SOURCE = 'CRM' , STATUS = 'TESTED', 'CANCELED'),

=IF(DEFECT_ID =- DEV_PLAN_ID AND DATA_SOURCE = 'DW' , STATUS = 'NOT TESTED', 'CANCELED'),

*/Else Statement

Re: Help in set analysis

gmoraleswit — Thu, 04 Sep 2014 20:03:07 GMT

try this:

if(Only({$<DATA_SOURCE={'CRM'}>} STATUS) <> Only({$<DATA_SOURCE-={'CRM'}>} STATUS), Only({$<DATA_SOURCE={'CRM'}>} STATUS),null())

Re: Help in set analysis

ali_hijazi — Thu, 04 Sep 2014 20:08:01 GMT

I tried this but it gave me allocated memory exceeded for the chart

Re: Help in set analysis

ali_hijazi — Thu, 04 Sep 2014 20:08:22 GMT

that's why I'm trying to avoid if else statement

Re: Re: Help in set analysis

gmoraleswit — Thu, 04 Sep 2014 20:19:43 GMT

Here is another solution,

I created a key field with DEFECT_ID and DEV_PLAN_ID and use this field in the expression:

only({$<DATA_SOURCE={'CRM'},key={"=count(distinct STATUS)>1"}>} STATUS)

see attach