Coppying fields to all rows with a common key

Fri, 10 Jul 2015 08:23:22 GMT

Hello all,

Basically, I an looking to do the opposite of what I posted here:

https://community.qlik.com/message/814377?et=watches.email.thread#814377‌

There is a unique key, which is the combination of invoice number, year and business unit. What I would like to do is to take whatever is on row 1 of a given invoice and coppy it to all other lines on the invoice. For example:

Now:

Invoice BusinessArea Year LineNum Debit/Credit

100 1 2015 1 D

100 1 2015 2 C

100 1 2015 3 D

100 2 2015 1 C

100 2 2015 2 C

101 3 2014 1 C

101 3 2014 2 C

101 3 2014 3 D

101 2 2014 1 D

101 2 2014 2 C

101 2 2014 3 C

101 2 2014 4 D

End result:

Invoice BusinessArea Year LineNum Debit/credit Debit/Credit2

100 1 2015 1 *same as D

100 1 2015 2 above D

100 1 2015 3 column D

100 2 2015 1 C

100 2 2015 2 C

101 3 2014 1 C

101 3 2014 2 C

101 3 2014 3 C

101 2 2014 1 D

101 2 2014 2 D

101 2 2014 3 D

101 2 2014 4 D

So basically, I want the debit or cedit indicator to be copied from line one to the other invoices with the same year, invoice and business area. Basically, the opposite of what I posted but this time, the field will not be blank, it needs to be coppied to a new field with the contents of lne number 1.

Many thanks in advance,

Dave

Re: Coppying fields to all rows with a common key

Sat, 11 Jul 2015 02:39:14 GMT

David, Please find the below piece of script.

Data:

LOAD * , Autonumber(Invoice&'|'&BusinessArea&'|'&Year) AS %Key Inline [

Invoice,BusinessArea,Year,LineNum,Debit/Credit

100,1,2015,1,D

100,1,2015,2,C

100,1,2015,3,D

100,2,2015,1,C

100,2,2015,2,C

101,3,2014,1,C

101,3,2014,2,C

101,3,2014,3,D

101,2,2014,1,D

101,2,2014,2,C

101,2,2014,3,C

101,2,2014,4,D

];

Left Join (Data)

LOAD

%Key ,

[Debit/Credit] AS [Debit/Credit2]

Resident Data Where LineNum = 1;

DROP Field %Key;

Note: this only works if u have LineNum 1 , 2 , 3 etc ..

Re: Coppying fields to all rows with a common key

Tue, 14 Jul 2015 13:00:46 GMT

Many thanks!!

topic Coppying fields to all rows with a common key in QlikView

Coppying fields to all rows with a common key

Re: Coppying fields to all rows with a common key

Re: Coppying fields to all rows with a common key