Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

berstrom
New 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
MVP & Luminary
MVP & Luminary

Re: Load Coloumns from Excel if existent

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

Re: Load Coloumns from Excel if existent

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

Highlighted
berstrom
New Contributor II

Re: Load Coloumns from Excel if existent

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

MVP & Luminary
MVP & Luminary

Re: Load Coloumns from Excel if existent

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