Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
I think Mapping load can help in this case. Please find the attached file.
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;
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
Massimo
Thank you, worked perfectly.