QlikView App Dev

[Anonymous]

Hi,

In my data, I have some customers who left their properties and they are replaced by the next tenant who continues receiving our service. Therefore, AccountNo and SerialNo change whilst Postcode and Reference will remain the same.

Now, the challenge is I need to transfer all the previous communications we had with the old customer to the new tenant's record.

For instance, in the example below I need to populate the Communication and Date columns for AccountNo 8228176 by the first letter we sent to AccountNo 2211823 on 30/01/2017.

And this is what I want to happend.

What is the best approach to achieve this?

Many thanks,

[sunny_talwar · Accepted solution]

You basically would need to use Peek/Previous to do this... an example can be found here:

Generating Missing Data In QlikView‌

Info on Peek/Previous can be found here:

Peek() vs Previous() – When to Use Each

[johnca]

I was thinking of FirstValue() (or FirstSortedValue()) in conjunction with Previous() or Peek(). A good exercise...let me see if I can figure this one out.

[florentina_doga]

see this script

aa:

load * inline [a1,a2,a3,data

a1,a2,a3,1/1/2017

a1,a2,a3,1/2/2017

a1,a2,a3,1/3/2017

a11,a22,,

a111,a223,,

];

NoConcatenate

temp:

load *,

if(a1<>Previous(a1) and a2<>Previous(a2) and numar<>1,Previous(peek('a3_new')),a3) as a3_new,

if(a1<>Previous(a1) and a2<>Previous(a2) and numar<>1,Previous(peek('data_new')),data) as data_new

;

load *, recno() as numar

resident aa order by a1 asc, a2 asc, data desc;

drop table aa;

[qliksus]

May be something like this

Data:
LOAD * ,Reference&PostalCode as Key INLINE [
    Accounnum, Serialno, Reference, PostalCode, Comminicaiton, Date
    2211823, B0081230, 30004532, LM12007, DD_Letter, 30/01/2017
    2211823, B0081230, 30004532, LM12007, RC_Letter, 10/05/2017
    2211823, B0081230, 30004532, LM12007, SD_Letter, 02/07/2017
    2211823, B0081230, 30004532, LM12007, SM_Letter, 12/08/2017
    828176, T0571654, 30004532, LM12007,,
];

left join (Data)
load

Reference&PostalCode as Key,
Comminicaiton&'-'&Date as OP

Resident Data;

load *

where Comminicaiton1<>'';

load * ,

if(Comminicaiton='',SubField(OP,'-',1),Comminicaiton) as Comminicaiton1,
if(Date='',SubField(OP,'-',2),Date) as Date1

Resident Data ;

DROP Table Data ;