topic Re: Logic problem in QlikView

Logic problem

woshua5550 — Wed, 25 Nov 2020 16:16:04 GMT

Hi there

This is a simple table what my souce table looks like

Below is what i would like to achieve:

If i choose AF1 in the filter, then we hope it can return the number:2

Because once we chosed AF1, then all the values 1 can been seen as 0. In this situation, ID 4 and 7 will have 0 for all hard checks, so 2 cases is the number we need

If we choose UW1 and UW2 in the filter part, then it should return the number:4

Because UW1 and UW2 are chosed in the filter, all the values in thest two column will change to 0. In this situation: ID 1, ID 2, ID 8 and ID 9 will have all the column values with 0

plz help me , thx in advance.

Re: Logic problem

lironbaram — Sun, 04 Dec 2016 15:05:53 GMT

there is quite an easy solution

in your load script cross table your source table for example

[Sheet1$]:

CrossTable (Status,Data,1)

LOAD [ID],

[UW1],

[UW2],

[UW3],

[UW4],

[AF1],

[AF2],

[AF3],

[AF4]

FROM [lib://AttachedFiles/Book1.xls]

(biff, embedded labels, table is Sheet1$);

then in your model

crate a calculated dimension with this expression

aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID)

this will get you your answer

Re: Logic problem

woshua5550 — Mon, 05 Dec 2016 04:53:56 GMT

Hi Liron

thanks for your reply , I followed your step but i still can't get the right answer

counld you plz attach your test file ?

thx

Re: Logic problem

rido1421 — Mon, 05 Dec 2016 05:30:56 GMT

Hi Dave

Could you perhaps post the sample data?

What is the logic behind your "Hard Checks" , how do you get the result 2 when selecting AF1?

The way I understand it , you want to count all "1" in the selected field when all the other fields =0?

Re: Logic problem

woshua5550 — Mon, 05 Dec 2016 05:41:18 GMT

Hi , problem solved

"count(aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID))" is what i want

thank you for your advice , it's quite helpful

Re: Logic problem

woshua5550 — Mon, 05 Dec 2016 05:46:01 GMT

when i selected AF1 , all the values in AF1 can be seen as 0 , i want to count rows that all columns is 0 , so the answer is 2.