Dynamic header size in script load

[#1]

Hi all,

I'm loading data from several different sheets. Each sheet contains the same header/label names, but the number of lines in the header is different in each sheet. Is there a way to set the "header is xx" in the script load as a dynamic variable? Or to set some condition telling QV "Start loading when you get to a row (in a fixed column) with XX string"?

In addition, is there a way to tell the script to STOP loading once it reaches a specific string in a given column?

[#2]

If you have header size for each file, you can pass the header size as variable.

I believe, you can exit the script at any line not the middle of loading table. If you want restrict the no of lines may be you can pass your filters on the WHERE condition ..

[Josh_Good]

The attached script in the QVW will do this for you.

You need to set the parameters in the inline load table (this table could also be loaded from elsewhere).  Note a .xls needs Type biff and a .xlsx needs type ooxml

I"ve also attached the data so you can try out my script as is.

FileInfo:
Load * Inline [
FileName,                 SheetName,    HeaderSize,    EndField, EndString,        Type
Categories.xlsx,       Categories,      2,                                 CategoryName, Bath Clothes,   ooxml
Products.xls,             Products$,        4,                                 ProductName,          DSW,                          biff
];

For i = 0 to NoOfRows('FileInfo')-1

Let vHeader = peek('HeaderSize', $(i), 'FileInfo');
Let vFileName = peek('FileName', $(i), 'FileInfo');
Let vSheetName = peek('SheetName', $(i), 'FileInfo');
Let vEndField = peek('EndField', $(i), 'FileInfo');
Let vEndString = peek('EndString', $(i), 'FileInfo');
Let vType = peek('Type', $(i), 'FileInfo');

Temp$(vSheetName):
LOAD
RowNo() as ID,
*
FROM
$(vFileName)
($(vType), embedded labels, header is $(vHeader) lines, table is $(vSheetName));

IDFinder:
Load ID Resident Temp$(vSheetName)
Where $(vEndField) = '$(vEndString)';

Let vID = peek('ID', 0, 'IDFinder');

Drop Table IDFinder;

NoConcatenate
$(vSheetName):
Load *
Resident Temp$(vSheetName)
Where ID < $(vID);

Drop Table Temp$(vSheetName);

Drop Field ID;

Next;

Drop Table FileInfo;

[#4]

Thanks for the reply, this is great info.

What if the header size isn't known at run time? What if I want it to be determined by the script dynamically (e.g. in column 1, search for string "ProductName." Header = # of rows to get to ProductName, and use embedded labels from that row). Is that possible?

[Josh_Good]

I think you could adjust the script above to do so.  I would recommended starting with load the whole excel sheet including header row.  Then using the appropriate logic to find the header (this would depend on what you know about the data).  Remove the unneeded rows and then adjust the field names in the table to match the values wanted for the header (I'm thinking you would use a mapping table).

[Kotty]

I'm unable to type any variable into the statement below, aside from the sheet name.

($(vType), embedded labels, header is $(vHeader) lines, table is $(vSheetName));

However I use Qlik Sense Nov 2018 instead of QlikView. Could that be the reason?

Also sorry for the necro.

[juanpacou]

Hi, were you able in Qlik Sense to write a variable in the FROM Statement ?? If not. Do you know another solution for this Topic?

[jonathandienst]

The qualifiers should be fine, but the filename works differently in QS. It needs to be based on folder data connection and have a path based on the lib:// references for the connection. Something like this:

...
FROM [lib://yourConnection/$(vFileName)]
($(vType), embedded labels, header is $(vHeader) lines, table is [$(vSheetName)]);

 The square brackets will handle spaces in the file path and sheet name.

[juanpacou]

Hi,  thanks for the help.
My problem is not the the path itself but what comes afterwards. Qlik Sense is not reading the variable vHeaderSize properly and is marking it red as if it was an error.  Strangely enough, the script is running and working.

LOAD*
FROM [lib://SAP Transaktionen/Supply Chain/LC_Cost_Centers.xlsx]
(ooxml, embedded labels, header is $(vHeaderSize) lines, table is [6137]);

[Kotty]

Yep, that's exactly the issue I've been dealing with. Anecdotally I hadn't tried to run that code before but now I did and it works for me as well. So it might only be an incorrect syntax highlighting inside QS script editor.

I'm just surprised no one has ever come across this issue in QS before.