topic 'For Each' to load xls multiple files, a few columns does not have the same name in QlikView

'For Each' to load xls multiple files, a few columns does not have the same name

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everybody,

I load multiple excel files in a For Each loop.

Inside the loop I have a Load from Load then a Left join.

In 2 files one column name is not the one I should get.

Is there a way to test the existence of a column in a Load and when it doesn't exist to load another column (I cannot change the header of the excel files) ?

Re: 'For Each' to load xls multiple files, a few columns does not have the same name

m_woolf — Thu, 22 Feb 2018 15:40:41 GMT

Are the columns in the same order, with just the column names different?

Re: 'For Each' to load xls multiple files, a few columns does not have the same name

Anonymous — Thu, 22 Feb 2018 15:44:49 GMT

I would avoid load *, as I don't load some of the columns (that exit or not) on different xls.

Re: 'For Each' to load xls multiple files, a few columns does not have the same name

Digvijay_Singh — Thu, 22 Feb 2018 16:22:54 GMT

One work around could be to use exact column names, and if you want to make decisions at run time, you may allow Qlik to generate error, you can continue script execution through set ErrorMode=0; based on error code returned in script error variable you can write code to read new column etc to handle exception condition.

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/ErrorVariables/ScriptError.htm

Re: 'For Each' to load xls multiple files, a few columns does not have the same name

marcus_sommer — Thu, 22 Feb 2018 16:27:57 GMT

There is no way within a load to check if a field exists. This meant you need for such a checking an additional step in beforehand - maybe by loading one single record of the file with the wildcard * for the fields and then looping through the exist fieldnames and comparing them against your condition and branching appropriately.

If you know exactly which files are different you could also use a small if-loop on them to branch in different load-statements. Maybe a bit more easier could it be to load your files without labels and name the fields explicitly like:

A as AField,

B as BField,

...

- Marcus

Re: 'For Each' to load xls multiple files, a few columns does not have the same name

Anonymous — Thu, 22 Feb 2018 16:36:48 GMT

Thanks a lot, I finally managed to get the excel column name corrected.