Help with Qlik set analysis!

[Chari24]

Hello All,

Can you please help me with below.

I have a data set like below

Id,  name, check1, check2, check3,status

Aaa,  John,   1,0,0,    Active

Aaa,  John,  0,0,0,   Pending

Bbb, Sam,      1,1,1 , Pending

Ccc,  Eric, 0,0,0,    Active

Ccc,  Eric, 1,0,0,    Active

Ddd, Ros,1,0,0 , inactive

EEE,Frank,0,0,0, inactive

FFF,David, 1,1,1,inactive

FFF,David, 0,0,0,inactive

How can I identify ID which has Check1, Check2, Check3 has all values 0’s and also any one of them has value 1

Loot for example ID -AAA , CCC and FFF

Thank you for your support!

[Daniel_Castella]

Hi @Chari24 

I'm not understanding your output. Why is FFF there if it has not (0,0,0)?

For me to understand, the output you want is the following, right?

The Ids that have (0,0,0) and any of the following (1,0,0) (0,1,0) (0,0,1)

Also, in which visualization do you want it to be displayed?

Kind Regards

Daniel

[N30fyte]

Set analysis is applied to aggregated measures - all your data appears to be dimensions, so set analysis won't help.

Maybe something like this:

All '0', first column:

=if(check1='0' and check2='0' and check3 = '0',Id)

and uncheck 'Include null values'.

Name and status fields included

'Any 1', first column:

=if(check1='1' or check2='1' or check3 = '1', Id)

again, uncheck 'Include null values'

Name and status fields included

[fabdulazeez]

FFF does not satisfy the condition.

Is below the condition you are looking for. Do you expect data transform in data load script or UI.

Does below help
=Concat(Aggr(
If(
Max(If(check1=0 and check2=0 and check3=0,1,0))
and
Max(If(check1+check2+check3>0,1,0)),
Id
),
Id
),',')

[Chari24]

Sorry I have Corrected data with  FFF ,

also I want to show in table 

Thank you!

[rwunderlich]

It would be easiest to set a flag in the script that you could use in Set filtering however you want. You could do so in the script like:

Data:

Load * Inline [

Id,  name, check1, check2, check3,status

Aaa,  John,   1,0,0,    Active

Aaa,  John,  0,0,0,   Pending

Bbb, Sam,      1,1,1 , Pending

Ccc,  Eric, 0,0,0,    Active

Ccc,  Eric, 1,0,0,    Active

Ddd, Ros,1,0,0 , inactive

EEE,Frank,0,0,0, inactive

FFF,David, 1,1,1,inactive

FFF,David, 0,0,0,inactive

];

Join (Data)

Load

Id,

Min(checkSum) = 0 and Max(checkSum) > 0 as IsQualified

Group by Id

;

Load Id,

RangeSum(check1, check2, check3) as checkSum

Resident Data;

If you needed to do something just in the chart without modifying script, you could use an expression along the lines of:

if(Min(TOTAL<Id>RangeSum(check1, check2, check3)) = 0
and Max(TOTAL<Id>RangeSum(check1, check2, check3)) > 0
, 1, 0)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Amit_Prajapati]

Hi @Chari24 ,

From the script, you can create flags to identify records based on the values of the three checks.

1 first column:

=if(check1='1' or check2='1' or check3 = '1', Id) as 1PresentCheck

0 for all 

=if(check1='0' and check2='0' and check3 = '0',Id) as 1notPresent

These flags can then be used during calculations to filter or aggregate data accordingly.

Let me know if you're looking for a different logic or need help applying these flags in your expressions.

[Daniel_Castella]

Hi @Chari24 

Try this calculated dimension:

=aggr(If(count({<check1={0},check2={0},check3={0}>} distinct Id)*(
count({<check1={1}>+<check2={1}>+<check3={1}>} distinct Id))>=1,Id),Id)

If you uncheck the "Include Null Values" option for it, you end with a table like this:

Kind Regards

Daniel

[hanna_choi]

Hello @Chari24 

How about this ways?

In data modeling, sum Check 1, Check 2, and Check 3 to create a FLAG field for values that are zero.

And in the visualization, the IF clause only shows that the FLAG value is O.

[ Data Load Script ]

[ Table Visualization ] 

You need to disable "Null value include".