Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
RobinB1
Contributor
Contributor

Code block reusability in LOAD script

I have a LOAD script like:

 

Concatenate(Sales)

SELECT

aa,

bb

FROM $(vMyPath)file1.xlsx (ooxml, embedded labels, table is INPUT)

;

 

Concatenate(Sales)

SELECT

aa,

bb

FROM $(vMyPath)file2.xlsx (ooxml, embedded labels, table is INPUT)

;

 

I.e. 2 blocks of code where only the names of the excel source files differ. (How) Can I reuse the main part of the code to shorten the script and do something like:

 

Sub MySub(MyExcelFile)

    Concatenate(Sales)

    SELECT

    aa,

    bb

    FROM $(vMyPath)MyExcelFile (ooxml, embedded labels, table is INPUT)

    ;

end sub

 

call MySub(file1.xlsx)

call MySub(file2.xlsx)

2 Replies
marcus_sommer

In general you could outsource such things into sub-routines. Depending on the number of parameter you specify and/or how expensive check- and/or error-logics are included you could build logics which could be used very often and in many scenarios. But the more you includes the more complex becomes it. Therefore be careful with such approaches to not to get the opposite from making things easier.

In regard to your example you might be able to use wildcards within the filename, like:

load * from path\*.xlsx (ooxml, …);

or using it within a filelist-loop, like:

for each file in filelist('path\*.xlsx')
   load * from [$(file)] (ooxml, …);
next

- Marcus

RobinB1
Contributor
Contributor
Author

I solved this one myself. The correct syntax is (everything else as is):

  FROM $(vMyPath)$(MyExcelFile) (ooxml, embedded labels, table is INPUT)

...

call MySub('file1.xlsx')

call MySub('file2.xlsx')