Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

How to populate values from the previously loaded records?

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.

Data_1.png

And this is what I want to happend.

Data_2.png

What is the best approach to achieve this?

Many thanks,

1 Solution

Accepted Solutions

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

View solution in original post

4 Replies

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

View solution in original post

Specialist
Specialist

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.

Partner
Partner

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;

Specialist II
Specialist II

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 ;