topic Re: for loop crosstable with differing columns in QlikView

for loop crosstable with differing columns

Anonymous — Sun, 08 Jan 2017 18:48:54 GMT

Hi all,

I have been struggling with this issue for a while looking for a solution.

I am trying to load in data from 55 sheets from a total of 289 sheets in an excel spreadsheet.

I need the all columns from the 55 sheets to be transposed into one table using the CrosstTable load function except one column from each sheet to be the qualifier field. However, the qualifier field is not always the same for each sheet. I also want to use a loop as I do not want 55 different load statements in the script.

A few things to consider:

- The sheets do follow a specific naming convention but they will not change

- I know the column that is required for each sheet

I have provided an extract of what I am trying below - I think the IF statement is not the right way to go about it. Any help would really be appreciated.

for each vSheet in 'S1', 'D1' etc

Data:

CrossTable(x, value, 2)

LOAD

'$(vSheet)' as table,

if('$(vSheet)' = 'S1', Column1,

if('$(vSheet)' = 'D1', Column2, '-')) as datafield,

FROM

[Data]

(ooxml, embedded labels, table is $(vSheet), filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1)),

Remove(Col, Pos(Top, 1))

));

next;

Re: for loop crosstable with differing columns

vinieme12 — Sun, 08 Jan 2017 20:23:30 GMT

This will not work, because you are only renaming an existing column in the first line with the IF Statement you are not actually changing the Order in which the columns are loaded into Qlikview

Example

Load

if(a='1',a) as newname

,b,a,c,d /// because * will still load all the columns

;

I would suggest creating an Excel macro to re-arrange the columns in the workbook itself.

Create variables with list of fields in the Order you want to load them

Example

vSheet1Fields = Field3,Field1,field2,field4;

vSheet2Fields = Field4,Field1,field2,field3; and so on

and use the if statement to use the fieldlist depending on the sheetname (/// This would still be a pain considering the number of sheets you have)

Example;

Load

if(Sheet1 , $(vSheet1Fields)) //// You got the point , right?

Re: for loop crosstable with differing columns

MarcoWedel — Sun, 08 Jan 2017 23:37:38 GMT

please provide some sample data and your expected result as well.

thanks

regards

Marco

Re: for loop crosstable with differing columns

petter — Sun, 08 Jan 2017 23:41:33 GMT

Yes you approach should work out. Your doubts around the IF is right - there is a better way to handle it.

I would suggest using a table - either inline or from a spreadsheet or CSV-file. The following code has not been tested - just write out from the top of my head. Anyways it gives you an indication of an approach that should work.

Sheets:
LOAD * INLINE [
Sheet,Col
Sheet1, ColC
Sheet6, C2
SheetB, AAA
....
];

nRows = NoOfRows('Sheets');

vColList ='';
FOR i=1 TO nRows
    vSheet = Peek('Sheet',i,'Sheets');
    vCol = Peek('Col',i,'Sheets');
   
    // pad the colums in the columnlist with @ before and after the column name, needed for correct Match later.
    vColList = vColList & If(i=1,'',',') & Chr(39) & '@' & vCol & '@' & Chr(39) ;
    Temp_Data:
    CrossTable(x, value, 2)
    LOAD
          '$(vSheet)' AS Table,
          $(Col) AS Dim,
          *    // Will also load the $(Col) as a data column that turns into rows, remove them in the final load
    FROM
          [Data.xlsx] (ooxml, embedded labels, table is $(vSheet), filters(
              Remove(Row, Pos(Top, 3)),
              Remove(Row, Pos(Top, 2)),
              Remove(Row, Pos(Top, 1)),
              Remove(Col, Pos(Top, 1))
          ));
NEXT

// Exclude all the rows that were added for the double load of the Col (attribute column)
Data:
LOAD * RESIDENT Temp_Data
WHERE Match( '@' & x & '@' , $(vColList))=0 ;

DROP TABLE Temp_Data;

Hope this makes sense - if not just ask me to clarify...