topic Re: Verify column headers before reload in QlikView

Verify column headers before reload

Wed, 24 Sep 2014 16:04:06 GMT

My requirement is that, I have large number of excel files which I need to reload. But before loading, I need to check if all the column headers in all these files are correct (These are data files and in same format. All should have same column headers).

So, my question is how can I read column headers from excel in qlikview for each file and match with a reference array of column headers?

My Try: As I am very new to Qlikview and new to scripting, so couldn't get far. All I can do now is get all files one by one in my code from the directory where it is stored but not sure how to read column headers from them and compare it.

Thanks for any help

Re: Verify column headers before reload

MK_QSL — Wed, 24 Sep 2014 16:30:59 GMT

Let me have some clarification first....

You have multiple excel files having same type of data (may be for different dates or for different months) and want to load them in one lot but before loading you want to check whether all the files are having the same headers...

Considering that all your files are having headers in the same order...you can ignore the column header and load all other data but at the same time can give column header name using alias...

Let me give you one example...

You have Customer vs Sales data for every day....

The files are in

Sales22092014

Sales23092014

Sales24092014

now if you want to load all the files using column header... it looks like as

Load

Customer,

Country,
Region,

Sales,

InvoiceDate

From

Sales22092014.xlsx

(ooxml, embedded labels, table is Sheet1);

Instead of this you can use as below

LOAD

A as Customer,

B as Country,

C as Region,

D as Sales

FROM

Sales*.xlsx

(ooxml, explicit labels, table is Sheet1)

Where RecNo() <> 1;

=========================================================

Looks like I am still missing something... may be you don't want to load those files where the column headers are not matching... Kindly clarify the same.... will work on that...

=======================================================

If possible, please provide some sample files...

Re: Verify column headers before reload

Gysbert_Wassenaar — Wed, 24 Sep 2014 16:44:48 GMT

See this blog post: qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/

Note, the part you're interesting in is the paragraph that starts with:

How about this case? I want to load any sheet that contains the three columns “Sales”, “Year” and “Quarter”:

In your case you're not interested in just three fields, but your reference array of fields.

Re: Verify column headers before reload

Wed, 24 Sep 2014 17:16:19 GMT

Hi Manish,

Thanks for your reply. I have standard file which I use as benchmark to load other files and I wish to match the column headers with repect to the benchmarked file. If there is any blank header or column names do not match(wrong data) then data reload will fail stating the message why the reload failed and due to which file(i.e.name of the file) . If the validation is correct I should get a message 'validation is successful.'

Can you suggest any wider approach ?

Re: Verify column headers before reload

Wed, 24 Sep 2014 20:33:59 GMT

Hi Manish,

What is the advantage of using alias?