topic Re: Urgent! How to compare with difference period when each of the period has different number of records in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

woshua5550 — Sun, 08 Apr 2018 07:05:48 GMT

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

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

Anonymous — Tue, 10 Apr 2018 02:38:50 GMT

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

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

woshua5550 — Tue, 10 Apr 2018 05:20:38 GMT

Hi Peter

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

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

woshua5550 — Tue, 10 Apr 2018 05:22:18 GMT

this is the sample data