Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
avastani
Partner - Creator III
Partner - Creator III

load a field only if it exists in the data source

I want to be able to check in the load script if my excel source or a text file source has a particular column and then load it. The reason being I have a for loop around it that cycles through all the files in the directory and if one file doesn't have that column in the source, then my process errors out for that file but continues on.

I would like for the script to intelligently check if that column exists and only then load it.

any tips? thanks.

1 Solution

Accepted Solutions
Not applicable

Here's a solution. Not that intelligent, but it does the job if your tables always have the same starting field:

VanishingField = 'ProductCost';

AlwaysFirstColumn = 'ID';



TEMP:
LOAD Count(*) as Temp
FROM [Table.xls] (biff, embedded labels, table is Sheet1$, filters(
Transpose()
))
where $(AlwaysFirstColumn) = '$(VanishingField)';

IsFieldAvailable = peek('Temp');

DROP TABLE TEMP;

if IsFieldAvailable then
MyTable:
LOAD Field1, Field2, .... , $(VanishingField)
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

else

MyTable:
LOAD Field1, Field2, ....
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

endif

View solution in original post

4 Replies
Not applicable

Here's a solution. Not that intelligent, but it does the job if your tables always have the same starting field:

VanishingField = 'ProductCost';

AlwaysFirstColumn = 'ID';



TEMP:
LOAD Count(*) as Temp
FROM [Table.xls] (biff, embedded labels, table is Sheet1$, filters(
Transpose()
))
where $(AlwaysFirstColumn) = '$(VanishingField)';

IsFieldAvailable = peek('Temp');

DROP TABLE TEMP;

if IsFieldAvailable then
MyTable:
LOAD Field1, Field2, .... , $(VanishingField)
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

else

MyTable:
LOAD Field1, Field2, ....
FROM [Table.xls] (biff, embedded labels, table is Sheet1$);

endif

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

For tips on handing varitions in Excel columns and sheets, see:

http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html

The QV Cookbook also has examples based on the Blog post.

For the text file(s), does the file have embedded labels? If so, you could read the label line and parse that to see what the file contains.

-Rob

avastani
Partner - Creator III
Partner - Creator III
Author

Thanks Rob and LuciaN. I guess there's no smart way of doing it in the LOAD statement when loading the excel file using BIFF.

I wonder if it is possible with databases.

However, both solutions do serve purpose.

Not applicable

Yes, it is possible with databases. Rob's post about using "SQLTABLES" command is a general solution using ODBC.