topic Re: Apply function to all fields.... in QlikView

Apply function to all fields....

Tue, 11 Sep 2012 21:14:30 GMT

Hi; Here's a strange request, it may not be possible at all but what do you think?

I need to load data from excel files generated through a web app to my application on a monthly basis,

the problem is the column names sometimes are not always generated the same....

like one month is: Sales Date

and the next one is: Sales date

(check the upper case!!)

And i already write a script to Automatically load all the excel files (also historic files) but i get unwanted results because of this,

so is there any way to apply the function Capitalize() to all the Columns?

something to apply on a code like this (no individual column names):

load * from 'filename'.xls

Any ideas?.....

Thanks in advance.

Regards from Mexico.

Re: Apply function to all fields....

Tue, 11 Sep 2012 21:36:11 GMT

Yes, there are possibilities.

In the loadscript you can use wildcards.

example:

load *

FROM

*.xlsx

(ooxml, embedded labels, table is Sheet1);

This will load ALL excelfiles in the folder that your QVW is in.

It is like dos-commands (del *.xls wil delete all excelfiles)

hope it helps

Re: Apply function to all fields....

Anonymous — Tue, 11 Sep 2012 21:47:56 GMT

just and idea not sure if this suits your request. if the order of the columns does not change then what you do is while loading the excel sheet for the first time do not load with embeded labels . load with the none and in your script rename the columns in the way you wanted. for your reference check the screen shot.

Re: Apply function to all fields....

Wed, 12 Sep 2012 15:54:55 GMT

Thank´s Roberto and Vinay! For practical purposes i´ll take the second approach and load the files without labesl and standarize the column names.

Regards!

Re: Apply function to all fields....

Anonymous — Wed, 12 Sep 2012 15:56:24 GMT

no problem Angela