topic Re: RE:Multi load with names in QlikView

RE:Multi load with names

Chanty4u — Fri, 12 Aug 2016 10:38:38 GMT

Hi all ,

I have a folder with 5 excels files . i want to load all into single table

but my requirment is to load excel filename as a column name in result table.

Example.: below data i i have in 5 excel files .Filename is [ 20160101 .xlsx ,20160102.xlsx ..... ]

Re: RE:Multi load with names

sunny_talwar — Fri, 12 Aug 2016 10:43:04 GMT

Check this link out:

Loops in the Script

Re: RE:Multi load with names

Gysbert_Wassenaar — Fri, 12 Aug 2016 10:43:31 GMT

LOAD

Date(Date#(FileBaseName(),'YYYYDDMM'),'YYYYDDMM') as Date

FROM

*.xlsx (ooxml, embedded labels, table is Sheet1)

;

Re: RE:Multi load with names

its_anandrjs — Fri, 12 Aug 2016 10:47:27 GMT

Load your data like this

Load

FileBaseName() AS Date

From <Excel Source>;

Load

FileBaseName() AS Date

From <Excel Source Second File>;

Regards

Anand

Re: RE:Multi load with names

Chanty4u — Fri, 12 Aug 2016 10:51:18 GMT

thanks . but in future some more files will be adding to the folder so is this will work? or any other way to do this?

Re: RE:Multi load with names

its_anandrjs — Fri, 12 Aug 2016 10:57:20 GMT

If your excel file name column name are same and from single excel sheet ie. Sheet1 then try this code

LOAD *
FileBaseName() AS Date
FROM

(ooxml, embedded labels, table is Sheet1);

Regards

Anand

Re: RE:Multi load with names

sunny_talwar — Fri, 12 Aug 2016 11:00:53 GMT

Slightly unrelated, but I am seen HIC saying that it is better to use loops instead of wildcard to load multiple files because if you are using preceding load, wildcard won't work the way you would expect it to

Re: How to load from multiple text files

Re: RE:Multi load with names

its_anandrjs — Fri, 12 Aug 2016 11:04:57 GMT

For your excel file as seen check this code

LOAD Account,
OB,
Cr,
DB,
Date(Date#(FileBaseName(),'YYYYDDMM'),'YYYYDDMM') AS Date
FROM
[*.xlsx]
(ooxml, embedded labels, table is Sheet1);

Date field as Gysbert suggest you.

Regards

Anand

Re: RE:Multi load with names

Colin-Albert — Fri, 12 Aug 2016 13:52:16 GMT

One further advantage of using a loop rather than a wildcard to load multiple files, is that you can add a trace statement in the loop to include the source file in the log, so if an error occurs, the file being loaded can be identified.

With a wildcard, the log file does not show which file is being loaded it just shows "file*.xls"