topic Code block reusability in LOAD script in QlikView

Code block reusability in LOAD script

RobinB1 — Wed, 19 Jan 2022 12:46:22 GMT

I have a LOAD script like:

Concatenate(Sales)

SELECT

aa,

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

;

Concatenate(Sales)

SELECT

aa,

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,

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

;

end sub

call MySub(file1.xlsx)

call MySub(file2.xlsx)

Re: Code block reusability in LOAD script

marcus_sommer — Wed, 19 Jan 2022 18:32:51 GMT

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

Re: Code block reusability in LOAD script

RobinB1 — Mon, 24 Jan 2022 11:45:41 GMT

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')