Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have and uniqueID that can change segment overtime. I want to in a table show only those how have changed segment comparing current month with last month. Data is monthly.
Example
UniqueID Month Segment
1 1 A
1 2 B
2 1 C
2 1 C
I want to get a set that only displays UniqueID, Segment only where shift has happend. Result of above should be
UniqueID Month 1 Month 2
1 A B
Solutions I am thinking about is:
1. Do it at loading: Bulid array with UniqueID and Segment and month as columns (limited to 2 last months). Loop through array and add UniqueID and SegmentShiftFlag to table.
2. Using Pivot Table - but it seems like you cannot limited them to not displaying null values
3. Using Table - displaying only UniqueID having Segment Month 1 <> Segment Month 2
Any tips, please
You can try the following script to flag these fields:
Temp:
LOAD * INLINE [
UniqueID, Month, Segment
1, 1, A
1, 2, B
2, 1, C
2, 1, C
];
Table:
LOAD *,
If(Peek('UniqueID') = UniqueID, If(Peek('Segment') <> Segment, 1, 0)) as Flag
Resident Temp
Order By UniqueID;
DROP Table Temp;
HTH
Best,
Sunny
what's the result you want in this example?
UniqueID Month Segment
1 1 A
1 2 B
1 3 C
2 1 C
2 1 C
3 1 D
3 4 E
Thanks sunindia,
works fine except that the first record in each set of UniqueID do not get a flag status 1 so my list looks like this:
1, 1, A, -
1, 2, B, 1
2, 1, C, -
2, 2, C, 0 (altered Month for last row to be 2 to be more correct)
Could that be fixed still using Peek or do I have to loop through the data with do while or similar?
Massimo, the output I want is:
Currently I do not care about anything but current and previous month so I would not go through more than these months.
But making it more generic I would like to get a Flag (1) for all UniqueID that have different Segment from latest segment.
Taking about result on display I also want to be able to display
UniqueID Month 1 Month 2
1 A B
Filtering on Flag = 1
Thanks
Anders