Coppying fields to all rows with a common key

[#1]

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

[#2 · Accepted solution]

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

[#3]

Many thanks!!