Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create a flag to track whenever there is a change in fact value based on ID and Month year.And also show the previous fact value in separate column when ever there is chnage happened if not show its previous value.
Input:
Output:
Thanks in advance
May be this
Table:
LOAD [App ID],
Date(Month, 'MMM-YY') as Month,
Sales;
LOAD * INLINE [
App ID, Month, Sales
3233, 08/01/2019, 342
3233, 07/01/2019, 222
3233, 06/01/2019, 222
3233, 05/01/2019, 234
3233, 04/01/2019, 112
3233, 03/01/2019, 112
3233, 02/01/2019, 456
3233, 01/01/2019, 587
];
FinalTable:
LOAD *,
If([App ID] = Previous([App ID]), If(Sales = Previous(Sales), Peek('Previous Sales'), Peek('Sales')), Sales) as [Previous Sales],
If([App ID] = Previous([App ID]), If([Sales] = Previous([Sales]), 'N', 'Y'), 'Y') as [Change Flag]
Resident Table
Order By [App ID], Month;
DROP Table Table;
May be this
Table:
LOAD [App ID],
Date(Month, 'MMM-YY') as Month,
Sales;
LOAD * INLINE [
App ID, Month, Sales
3233, 08/01/2019, 342
3233, 07/01/2019, 222
3233, 06/01/2019, 222
3233, 05/01/2019, 234
3233, 04/01/2019, 112
3233, 03/01/2019, 112
3233, 02/01/2019, 456
3233, 01/01/2019, 587
];
FinalTable:
LOAD *,
If([App ID] = Previous([App ID]), If(Sales = Previous(Sales), Peek('Previous Sales'), Peek('Sales')), Sales) as [Previous Sales],
If([App ID] = Previous([App ID]), If([Sales] = Previous([Sales]), 'N', 'Y'), 'Y') as [Change Flag]
Resident Table
Order By [App ID], Month;
DROP Table Table;
Thanks Sunny