Load Coloumns from Excel if existent

[berstrom]

Hey!

I'm trying to load columns from Excel under the condition that they exist. For the following code this means if the column with numbers "1", "2" and "3" exist, Qlikview should load them, but if for instance "2" does not exist it should not try to load them. As for now I get the error message "2" does not exist, which I do not want displayed.

for a = 1 to 3
LOAD [$(a)]
FROM [$(Path)\File.xlsx]
(ooxml, embedded labels, table is [Sheet]);
next

Does anyone know how to fix this?

Best regards

[anushree1]

Please use Exists Function description: https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFun...

For Eg:

Test:
load * Inline
[A
1
2
3
];

Data:
Load * Inline
[A, Dept
1, "asd"
2,"fgh"
3,"uio"
6,"yuo"
7,"iou"

]
where Exists(A,A);
Drop Table Test;

This will result in table having values 1,2, 3 as values for Col A ; other values of A will be ignored as its not listed in the Test Table.

Hope this helps

[berstrom]

Hey,

thanks for the help, but I might have formulated the question wrong.

I want Qlikview to load the fields "1", "2" or "3" if existent (these are the column names and not values within a column, so there are 3 colums to check) and if not existent Qlikview should not try to load the respective column.

Best regards

[marcus_sommer · Accepted solution]

It's not possible from inside the load-statement else you will need additionally logic on the outside which does the check and branched into different loadings and/or build a load-statement (respectively the essential parts of it) on the fly. I hope the following will be useful for you to adapt it to your real requirements:

temp: first 1 load * FROM [$(Path)\File.xlsx] (ooxml, embedded labels, table is [Sheet]);

for a = 1 to 3
   if fieldnumber('$(a)', 'temp') then
      LOAD [$(a)] FROM [$(Path)\File.xlsx] (ooxml, embedded labels, table is [Sheet]);
   end if
next

drop tables temp;

- Marcus