topic Load Coloumns from Excel if existent in QlikView

Load Coloumns from Excel if existent

berstrom — Sat, 16 Nov 2024 03:05:45 GMT

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

Re: Load Coloumns from Excel if existent

anushree1 — Thu, 04 Jul 2019 10:20:23 GMT

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

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

Re: Load Coloumns from Excel if existent

berstrom — Thu, 04 Jul 2019 12:48:18 GMT

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

Re: Load Coloumns from Excel if existent

marcus_sommer — Thu, 04 Jul 2019 15:02:45 GMT

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