Comparing dimensions of different period and only shown where they differ

[#1]

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

[sunny_talwar]

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

[maxgro]

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

[#4]

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?

[#5]

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