Comparison from different period of time

[Anonymous]

Hi everyone,

I am a new user of Qlikview. Basically, To make it simply, I have below table:

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

My question here, is it possible to make an indicator which showing any changes for the name by period? I want a table like this:

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

[Anonymous]

Hi, Peter,

Something like this

Regards,

[Anonymous]

Hi Mauri,

I don't have my laptop right now, but I want to have a look at the coding. Would you mind to post the code here?

Regards

[shiveshsingh]

What's the logic of add and remove?

[Anonymous]

The logic of add and remove happens when there is some changes in 'name' when comparing to the previous 'period'. e.g. In 201803, new 'name' of Mary & Susan occur when compare to previous period of 201802, so I want an indicator for 'add'. At the same time, in 201803, 'name' of Paul and Peter drop out when compare to previous period of 201802, so I want an indicator for 'remove'.

Regards

[Anonymous]

Hi Mauri,

It seems doesn't work. 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 (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 (add Peter and Paul)

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   

[uacg0009]

Hi Peter,

Please see my attachment, hope that's what you want.

Thanks.

Aiolos

[Anonymous]

Hi Aiolos,

Thanks for your help.

How about if I have more than one Customer ID with similar case as above? The coding seems only works for one Customer ID only.

Regards

Ryan

[uacg0009]

Hi Peter,

Have you tried my scripts for more than one Customer ID?

I have tried it using attachment file, it works.

Thanks.

Aiolos

[Anonymous]

Hi Aiolos,

So which means I need to order by 'Period' first and then order by 'Customer ID', right?

Btw, according to the table from left, the highlighted 'Period' should be '201803', isn't it?

Regards