Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
sunny_talwar

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
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.

florentina_doga
Partner - Creator III
Partner - Creator III

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
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 ;