Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Trying to figure out how to separate arguments within a cell from an Excel spreadsheet upon the load into Qlikview app. I have attached a sample. I will need the script rather than the .qvw. I was familiar with the Peek function when I have data in separate rows. This question is when the arguments are in the same row. Here is what I started with for scripting:
if(len([Dept. #])>0,[Dept. #],peek('1')) as [SAM Dept. #],
if(len([Station / TLID])>0,[Station / TLID],peek('1')) as [SAM Station / TLID],
if(len([Q-note #])>0,[Q-note #],peek('1')) as [SAM Q-note #],
if(len([Dev.Description])>0,[Dev.Description],peek('1')) as [SAM Dev.Description],
if(len([Deviation])>0,[Deviation],peek('1')) as [SAM Deviation Owner],
if(len([Reference ETN])>0,[Reference ETN],peek('1')) as [SAM Reference ETN],
if(len([Material])>0,[Material],peek('1')) as [SAM Material In],
Qty as [Qty In],
Sloc as [Sloc In],
Material1 as [Material Out],
Qty1 as [Qty Out],
Sloc1 as [Sloc Out],
[Dev. Request Status],
[Dev. Start Date],
[Dev. Actual End Date (Q-note closed)],
[NCCA #],
[Decision #],
[Decision effective Date]
Hi Todd,
I think you are looking for the subfield function, which is able to extract parts of a field and generate multiple rows out of one source row including one of the subfields each:
LOAD [Dept. #],
[Station / TLID],
[Q-note #],
Dev.Description,
Deviation,
SubField([Reference ETN], ' ') as [Reference ETN],
Material,
Qty,
Sloc,
Material1,
Qty1,
Sloc1,
[Dev. Request Status],
[Dev. Start Date],
[Dev. Actual End Date (Q-note closed)],
[NCCA #],
[Decision #],
[Decision effective Date]
FROM [http://community.qlik.com/servlet/JiveServlet/download/538911-108926/Qlikview%20question.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 2))));
so your excel source
should be loaded like
hope this helps
regards
Marco
Hi Todd,
I am not sure if I have understood your query correctly.
To me, the IN and OUT columns are in the second row of the excel sheet.
You are correct. For the header I have scripted that with changing to capture on the load. so when I load it I delete that row. My question is about the subsequent rows where I want to make row 3 into 2 separate rows because of column 6 (Reference ETN) has two arguments.
Hi Todd,
I think you are looking for the subfield function, which is able to extract parts of a field and generate multiple rows out of one source row including one of the subfields each:
LOAD [Dept. #],
[Station / TLID],
[Q-note #],
Dev.Description,
Deviation,
SubField([Reference ETN], ' ') as [Reference ETN],
Material,
Qty,
Sloc,
Material1,
Qty1,
Sloc1,
[Dev. Request Status],
[Dev. Start Date],
[Dev. Actual End Date (Q-note closed)],
[NCCA #],
[Decision #],
[Decision effective Date]
FROM [http://community.qlik.com/servlet/JiveServlet/download/538911-108926/Qlikview%20question.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 2))));
so your excel source
should be loaded like
hope this helps
regards
Marco
Hello, Todd.
Try something like this:
Data:
LOAD [Dept. #],
[Station / TLID],
[Q-note #],
Dev.Description,
Deviation,
SubField([Reference ETN], ' ', IterNo()) as [Reference ETN],
Material,
Qty,
Sloc,
Material1,
Qty1,
Sloc1,
[Dev. Request Status],
[Dev. Start Date],
[Dev. Actual End Date (Q-note closed)],
[NCCA #],
[Decision #],
[Decision effective Date]
FROM
[Qlikview question.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 2))))
While IterNo() <= 2;
Regards.