topic Re: Compare Previous Values in QlikView

Compare Previous Values

mahendragaur — Wed, 12 Apr 2017 06:34:41 GMT

Hello,

Below is my requirement Name, Group, Date. I sorted the data based on Name (Asc) and Date (Dsc). Yellow highlighted is my latest group and I want to check the number of occurrence of the latest group for the same user and if it finds then in next column (Next Step), it should update the previous value of Group else 0.

Please let me know which function I can use to compare the previous value in whole column based on same name.

Name	Group	Date	Next Step
Mahendra	111	1/25/2016	0
Mahendra	11	1/24/2016	0
Mahendra	12	1/23/2016	0
Mahendra	11	1/22/2016	0
Mahendra	111	1/21/2016	11
Mahendra	11	1/20/2016	0
Mahendra	111	1/15/2016	11
Mahendra	111	1/12/2016	111
Nishant	11	3/20/2016	0
Nishant	11	1/19/2016	11
Nishant	111	1/18/2016	0
Nishant	1	10/15/2012	0

Re: Compare Previous Values

Anil_Babu_Samineni — Wed, 12 Apr 2017 06:51:05 GMT

We have functions Aggr() and Above() combination here

Re: Compare Previous Values

mahendragaur — Wed, 12 Apr 2017 07:13:36 GMT

I want to compare the current value of Group with the whole column if it matches then it should return the previous value in the next step

Re: Compare Previous Values

Anil_Babu_Samineni — Wed, 12 Apr 2017 08:01:19 GMT

Yes, We can use like below

If(Aggr(Value = CurreneValue,Group), Before(Value)','NA')

Re: Compare Previous Values

sunny_talwar — Wed, 12 Apr 2017 08:02:17 GMT

Not sure I understand what you are looking to do... Would you be able to expand and elaborate with an example?

Re: Compare Previous Values

mahendragaur — Wed, 12 Apr 2017 08:42:09 GMT

Yellow highlighted is the latest value of group i.e. 111 and if the same value is present in the same column(Group) and for the same name (Mahendra) then it should update the previous value in the column (Next Step). like it is doing 11 and 111 when it found match else 0.

Re: Compare Previous Values

Kushal_Chawda — Wed, 12 Apr 2017 10:26:16 GMT

Data:

LOAD Name,

Group,

Date

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD Name,

max(Date) as Date,

1 as Flag

Resident Data

Group by Name;

Left Join(Data)

LOAD

Name,

Group as NewGroup

Resident Data

where Flag=1;

Group:

LOAD *,

if(RowNo()=1 or Name<>Previous(Name),0,if(Group=NewGroup,Peek(Group),0)) as Output

Resident Data

order by Name,Date desc;

DROP Table Data;

Update: Script and application updated

Re: Compare Previous Values

mahendragaur — Thu, 13 Apr 2017 05:29:54 GMT

Thanks Buddy. This is what I was expecting

Re: Compare Previous Values

Kushal_Chawda — Thu, 13 Apr 2017 05:51:17 GMT

Glad that it was helpful