Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
berstrom
Contributor II
Contributor II

Load Coloumns from Excel if existent

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

 

Labels (4)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
anushree1
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

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

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