topic Re: Find intersection between previous values in QlikView

Find intersection between previous values

agni_gold — Sun, 08 Jan 2017 07:48:21 GMT

Hi Friends,

I have one situation ,

I have below data for Month and ID

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	4	null
May-16	4	4

Now i want each row should compare its previous 3 rows and find intersection value , i have placed required output in one column.

Example : For Feb-2016 , it will compare Dec-2015,Jan-2016 and Feb-2016 any ID: 3 is common in between , so 3 should return in front of Feb-2016 , else null

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 10:15:15 GMT

See attached

FACT:

LOAD Month,

ID,

Month&'_'&ID as KEYField

FROM

(ooxml, embedded labels, table is Sheet1);

left join(FACT)

Temp:

LOAD

ID,

ID as FLAG,

MAX(Month)&'_'&ID as KEYField

Resident FACT

Group by ID;

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 10:38:24 GMT

in Straight table you can try

=if(ID = Rangesum(above(ID,0,RowNo()))/3,ID,0)

Re: Find intersection between previous values

agni_gold — Sun, 08 Jan 2017 13:21:19 GMT

But here i am not getting any intersection.

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 13:29:59 GMT

Not sure, what if?

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	null
May-16	5	?
Jun-16	4	?
Jul-16	4	?

Re: Find intersection between previous values

agni_gold — Sun, 08 Jan 2017 13:54:35 GMT

Yes this i want , but it is not happening.

Can you please share app with set analysis.

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 15:23:20 GMT

what should be the output here?

what if?

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	??
May-16	5	??
Jun-16	4	??
Jul-16	4	??

Re: Find intersection between previous values

agni_gold — Sun, 08 Jan 2017 15:54:48 GMT

Please see below

Month	ID	Required_Output
Dec-15	3	null
Jan-16	3	null
Feb-16	3	3
Mar-16	4	null
Apr-16	5	null
May-16	5	null
Jun-16	4	null
Jul-16	4	null

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 16:40:00 GMT

OK, try the below

FACT:

LOAD Month,

ID,

if(RangeSum(ID,Peek(ID),Peek(ID,-2))/3=ID,ID,null()) as Flag

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Find intersection between previous values

agni_gold — Sun, 08 Jan 2017 18:15:22 GMT

Can we do it on front end , in set analysis ?

Re: Find intersection between previous values

vinieme12 — Sun, 08 Jan 2017 20:09:15 GMT

Attached updated App

SUM(AGGR(IF(ID = rangesum(Above(ID,0,3))/3 , ID,null()),Month))

Re: Find intersection between previous values

agni_gold — Mon, 09 Jan 2017 07:39:21 GMT

Thanks ,

Little bit change in requirement ,

Month	ID	Required_Output
Dec-15	3,2,1	null
Jan-16	3,4,8	null
Feb-16	2,3	3
Mar-16	9,2,4	null
Apr-16	6,1,5	null
May-16	6,5	null
Jun-16	6,5,4	null
Jul-16	5,4	5

to find common between strings type structure.