Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Kotty
Contributor II
Contributor II

Dynamic header size in a load script

Hello,

I'm having a trouble to set a header size of an XLSX file dynamically in a load script. For example I want to set the 'header line is' part of the file specification to: 

header line is $(vHeaderSize) lines

where a variable vHeaderSize  is always an integer. However QS doesn't seem to accept anything but a whole number in that spot. However some older topics on this forums suggest that it should be possible:

At least in QlikView anyway. Is QS any different in this regard? Or has it been changed with some later update?

Would anyone be able to help me with this?

Thank you.

Labels (2)
1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

Hi @Kotty  and @brunobertels 

You are talking about having a dynamic Format Specification; you can do that by assigning the whole format specification to a variable, actually to a SET variable that behaves like a function; below the Format Specification Set variable definition:

 

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

 

Notice that the whole Load Specification was assigned to a Set variable named: vLoadSpec; with the variable vHeaderSize becoming the placeholder for the Header Size; then we could use it like this:

 

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

 

 When we set the variable vHeaderSize to a value, followed by assigning our Set variable (vLoadSpec) to the variable rtLoadSpec (run time load spec), the next time we need to change the header location we could write:

 

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

 

Finally, our Load statement reference the rtLoadSpec variable like shown below:

 

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 
Header_at_5:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-01.xlsx]
$(rtLoadSpec);

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 

Header_at_6:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-02.xlsx]
$(rtLoadSpec);

 

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

9 Replies
Kotty
Contributor II
Contributor II
Author

Bump

Kotty
Contributor II
Contributor II
Author

Does nobody know the answer? 😞

brunobertels
Master
Master

Hi 

Got the same issue today , it seem's like qlik sense does'nt accept this syntax, and only wait for an integer as number of line header. 

Unless you have find another solution since april 2019 ? 

Regards

ArnadoSandoval
Specialist II
Specialist II

Hi @Kotty  and @brunobertels 

You are talking about having a dynamic Format Specification; you can do that by assigning the whole format specification to a variable, actually to a SET variable that behaves like a function; below the Format Specification Set variable definition:

 

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

 

Notice that the whole Load Specification was assigned to a Set variable named: vLoadSpec; with the variable vHeaderSize becoming the placeholder for the Header Size; then we could use it like this:

 

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

 

 When we set the variable vHeaderSize to a value, followed by assigning our Set variable (vLoadSpec) to the variable rtLoadSpec (run time load spec), the next time we need to change the header location we could write:

 

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

 

Finally, our Load statement reference the rtLoadSpec variable like shown below:

 

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 
Header_at_5:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-01.xlsx]
$(rtLoadSpec);

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 

Header_at_6:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-02.xlsx]
$(rtLoadSpec);

 

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Kotty
Contributor II
Contributor II
Author

It works like a charm! You've been very helpful, thank you!

ArnadoSandoval
Specialist II
Specialist II

@Kotty 

Cool, I answered a one year, one month and 19 days old question 😎

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Kotty
Contributor II
Contributor II
Author

Time flies 😀 

Before you've provided me your awesome solution I simply used the "if" function that would run appropriate load script with hardcoded header size based on the header size that'd been passed from the call script as an argument. 

There've been only 3 possibilities for the row position of the header so far so the original script wasn't terribly long but your solution is a lot more elegant.

brunobertels
Master
Master

Hi All 

Thanks @ArnadoSandoval for your solution. I have 50 % of my requierment solved with your response. 

 

I Need to implement this now in a script where i load each month an excel file with multiple sheet ( between 4 and 6 but it can changed from one month to another and for some Reason the header is floating between line 8 and 9 due to parameter info in the first lines of the sheet. 

So  I have an ODBC connection and a for loop to check and load the different sheet and need now to implement your script. 

the header  line number is checking with a fieldindex( field ,'value') statement 

Thank again 

 

Bruno

ArnadoSandoval
Specialist II
Specialist II

Hi @brunobertels 

I suggest you to create a new topic for your next question, also would you elaborate more when you wrote

the header  line number is checking with a fieldindex( field ,'value') statement 

I was unable to translate that to code in my mind!

Regards, 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.