topic Re: Re: Separating arguments from a cell in Excel worksheet upon loading in Qlikview in QlikView

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

Anonymous — Fri, 30 May 2014 14:31:32 GMT

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]

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

anandathome — Fri, 30 May 2014 14:47:42 GMT

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.

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

Anonymous — Fri, 30 May 2014 14:52:56 GMT

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.

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

MarcoWedel — Sun, 01 Jun 2014 21:28:51 GMT

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

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

Anonymous — Sun, 01 Jun 2014 23:17:19 GMT

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.