topic load a field only if it exists in the data source in QlikView

load a field only if it exists in the data source

avastani — Thu, 17 Dec 2009 05:12:31 GMT

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.

load a field only if it exists in the data source

Thu, 17 Dec 2009 05:38:48 GMT

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

load a field only if it exists in the data source

rwunderlich — Thu, 17 Dec 2009 06:27:27 GMT

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

load a field only if it exists in the data source

avastani — Thu, 17 Dec 2009 06:49:11 GMT

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.

load a field only if it exists in the data source

Thu, 17 Dec 2009 16:27:49 GMT

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