topic Re: How to populate values from the previously loaded records? in QlikView

How to populate values from the previously loaded records?

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

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,

Re: How to populate values from the previously loaded records?

sunny_talwar — Tue, 05 Sep 2017 16:37:55 GMT

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

Re: How to populate values from the previously loaded records?

johnca — Wed, 06 Sep 2017 02:51:34 GMT

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.

Re: How to populate values from the previously loaded records?

florentina_doga — Wed, 06 Sep 2017 11:56:41 GMT

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;

Re: How to populate values from the previously loaded records?

qliksus — Thu, 07 Sep 2017 06:15:30 GMT

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 ;