Compare Previous Values

[mahendragaur]

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

[Anil_Babu_Samineni]

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

[mahendragaur]

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

[Anil_Babu_Samineni]

Yes, We can use like below

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

[sunny_talwar]

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

[mahendragaur]

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.

[Kushal_Chawda · Accepted solution]

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

[mahendragaur]

Thanks Buddy. This is what I was expecting

[Kushal_Chawda]

Glad that it was helpful