Urgent! How to compare with difference period when each of the period has different number of records

[Anonymous]

Hi everyone,

Need urgent help on below. Let me explain a bit more about what I want...

Customer ID	Period	Name
A	201801	Peter
A	201801	Paul
A	201802	Peter
A	201802	Paul
A	201803	Mary
A	201803	Susan
A	201804	Peter
A	201804	Paul
A	201804	Mary
A	201804	Susan

As shown from the table above:

(1) Compare to Period 201801, Period 201802 has the same records in Name (e.g. Peter and Paul), so there is no change.

(2) Compare to Period 201802, Period 201803 change in Name (e.g. no Peter and Paul / and add Mary and Susan), so I want the table showing 4 records with Period of 201803 (remove Peter and Paul / and add Mary and Susan)

(3) Compare to Period 201803, Period 201804 change in Name (e.g. add Peter and Paul), so I want the table showing 2 records with period of 201804 (add Peter and Paul)

Below is the information that I want to show in the table:   

Customer ID	Period	Name	Indicator
A	201803	Mary	Add
A	201803	Susan	Add
A	201803	Peter	Remove
A	201803	Paul	Remove
A	201804	Peter	Add
A	201804	Paul	Add

Can anyone help on this?

Regards

[woshua5550]

Hi Peter

Try like this:

Rawdata:

LOAD *,

CustomerID & Period & Name as %KEY1,

CustomerID & Text(Period - 1) & Name as %KEY2,

CustomerID & Text(Period + 1) & Name as %KEY3

Inline [

CustomerID, Period, Name

A, 201801, Peter

A, 201801, Paul

A, 201802, Peter

A, 201802, Paul

A, 201803, Mary

A, 201803, Susan

A, 201804, Peter

A, 201804, Paul

A, 201804, Mary

A, 201804, Susan

];

TMP:

LOAD Min(Period) as MinPeriod,

Max(Period) as MaxPeriod

Resident Rawdata;

Let vMinPeriod = Peek('MinPeriod',0,'TMP');

Let vMaxPeriod = Peek('MaxPeriod',0,'TMP');

Result:

LOAD CustomerID,

Period,

Name,

'Add' as Indicator

Resident Rawdata

Where NOT Exists (%KEY1,%KEY2) and Period <> $(vMinPeriod)

;

LOAD CustomerID,

Period + 1 as Period,

Name,

'Remove' as Indicator

Resident Rawdata

Where NOT Exists (%KEY1,%KEY3) and Period <> $(vMaxPeriod)

;

DROP Tables Rawdata,TMP;

plz find my test file attached

[Anonymous]

Hi Dave,

It words only when the 'Period' is consecutively occur, e.g. 201801 > 201802 > 201803.

However, it seems not work when the 'Period' occur like this e.g. 201801 > 201803 > 201808

Regards

[woshua5550]

Hi Peter

plz look at this , it will works fine when the period is not consecutively occur

[woshua5550]

this is the sample data