Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

deere2013
New Contributor II

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

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]

Tags (1)
1 Solution

Accepted Solutions

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

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

QlikCommunity_Thread_120393_Pic2.JPG.jpg

should be loaded like

QlikCommunity_Thread_120393_Pic1.JPG.jpg

hope this helps

regards

Marco

4 Replies
anandathome
Contributor

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

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.

deere2013
New Contributor II

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

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

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

QlikCommunity_Thread_120393_Pic2.JPG.jpg

should be loaded like

QlikCommunity_Thread_120393_Pic1.JPG.jpg

hope this helps

regards

Marco

bruno_montenegr
Contributor III

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

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.

Community Browser