Separating arguments from a cell in Excel worksheet upon loading in Qlikview

[Anonymous]

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]

[anandathome]

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.

[Anonymous]

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.

[MarcoWedel · Accepted solution]

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

[Anonymous]

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.