How to copy data of one row to all relevant rows

[#1]

Hi everyone

Please see attached screenshot of script and then the result of the data after the load.

I have broken down the excel sheet into what it looks like after the load and how it should look like.

It is very easy to do in Excel but not so easy in Qlikview.

Could someone assist me in editing the script so that my data looks like the data on the excel sheet.

Basically what I need is the following:

In field C0 you will find 3 results ranging from 1 - 3.

Where C0 = 1 there is a reference number in field C8.

I want this reference number to apply to all lines where the tran_no field is the same.

Thank you

Christo

[sudeepkm]

I think Mapping load can help in this case. Please find the attached file.

[maxgro · Accepted solution]

Directory;

source:
LOAD create_date,
    customer,
    tran_no,
    status,
    line_no,
    C0,
    C10,
    C4,
    C11,
    C5,
    C7,
    C8
FROM
TB04_20140505_152540.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6))
));

map:
Mapping LOAD tran_no, C8 Resident source where C0 = 1;

table:
NoConcatenate
LOAD create_date,
    customer,
    tran_no,
    status,
    line_no,
    C0,
    C10,
    C4,
    C11,
    C5,
    C7,
    ApplyMap('map', tran_no) as C8
Resident
  source
where C0 <> 1;

DROP Table source;   

[MarcoWedel]

Hi Christo,

one solution could be:

tabInput:
LOAD * FROM
[http://community.qlik.com/servlet/JiveServlet/download/522066-104935/TB04_20140505_152540.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Remove(Row, Pos(Top, 10)),Remove(Row, Pos(Top, 9)),Remove(Row, Pos(Top, 8)),Remove(Row, Pos(Top, 7)),Remove(Row, Pos(Top, 6))));

tabOutput:
LOAD create_date, 
     customer, 
     tran_no, 
     status, 
     line_no, 
     C0, 
     C10, 
     C4, 
     C11, 
     C5, 
     C7
Resident tabInput
Where C0<>1;

Left Join (tabOutput)

LOAD
  tran_no,
  C8
Resident tabInput
Where C0=1;

DROP Table tabInput;

hope this helps

regards

Marco

[#5]

Massimo

Thank you, worked perfectly.