topic Re: Loading & Concatenate All Files from folder in QlikView

Loading & Concatenate All Files from folder

Tue, 04 Nov 2014 11:09:05 GMT

Hi All

I'm having an issue and i have trailed the internet for an answer but cant find one. Can you help??

Scenario

I have this folder and every month a new file(Excel) will be saved into it by someone else.

This month

IRL 1.1 Sep

IRL 1.1 Oct

IRL 1.1 Nov

Next month

IRL 1.1 Sep

IRL 1.1 Oct

IRL 1.1 Nov

IRL 1.1 Dec

and so on...... I want to write a script that will load

I want to write a script that will load & concatenate all the files in the folder every month.

I don't want to have to re point my script each month. All i want to have to do is refresh my script and Qlikview will realise that there is now four files in the folder and load and merge them all.

"Things to remember"

Each of these files will have the same number of columns and will have the same column headings.
Ref numbers may re-occur in two or more of the data sources. (This is OK as there will be a date stamp in each file which will let me know which month it refers to).
These are all excel files and are named exactly as above.

I have read about loops etc but I'm not really sure.

Can you help on this please?

Thanks

Andrew

Re: Loading & Concatenate All Files from folder

alexandros17 — Tue, 04 Nov 2014 11:14:57 GMT

let path_Pallet = '..\..\qlik\myFile*.xlsx';

for each File in filelist (path_Pallet)

MyFile:

LOAD *
FROM $(File)

(ooxml , embedded labels , table is Foglio1);
next File

Let me know

Re: Loading & Concatenate All Files from folder

MK_QSL — Tue, 04 Nov 2014 11:28:14 GMT

LOAD

FROM

[IRL 1.1 *.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Month(Date#(TextBetween(FileName(),' ','.',2),'MMM')) <= Month(Today());

Re: Loading & Concatenate All Files from folder

Tue, 04 Nov 2014 11:36:05 GMT

Hi Thanks for the above.

That is still only seems to be loading in one of my data sources.

My script

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

let path_Pallet='\\HORODC26\DFSROOT\TEAM DATA\HORSHAM.opd\Indemnity Performance\Functional - Claims Insight\Exception Reporting\Qlikview\IRL\Extracts\IRL 1.1\IRL 1.1 Sep.xlsx';

for each file in FileList(path_Pallet)

IRL1:

load*

From

(ooxml, embedded labels, table is Sheet1);

next file

Re: Loading & Concatenate All Files from folder

MK_QSL — Tue, 04 Nov 2014 11:42:28 GMT

for each file in FileList(path_Pallet)

IRL1:

load*

From

(ooxml, embedded labels, table is Sheet1);

next file

Re: Loading & Concatenate All Files from folder

Tue, 04 Nov 2014 11:49:50 GMT

Hi Manish

Thanks for the above, but my file names will not always be month names.

Also in your example i cant see where the your script has a file path to go and grab the report??

Thanks

Andrew

Re: Loading & Concatenate All Files from folder

MK_QSL — Tue, 04 Nov 2014 11:53:42 GMT

I have used relative path.... you can obviously use my script and can use the absolute path instead..

Re: Loading & Concatenate All Files from folder

Tue, 04 Nov 2014 12:09:12 GMT

I still cant seem to get it working. I dont know what i am doing wrong????

My current script

*******************************************************************************************************************************

let path_pallet='S:\HORSHAM.opd\Indemnity Performance\Functional - Claims Insight\Exception Reporting\Qlikview\IRL\Extracts\IRL 1.1.xlsx'; (((This is the path of the the folder)))

for each file in FileList(path_pallet)

IRL1:

LOAD*

from $(file)

(ooxml, embedded labels, table is Sheet1);

next file

*******************************************************************************************************************************

Any thoughts, i cant use anything which bases around date as the files might not have dates in the name

Re: Loading & Concatenate All Files from folder

Tue, 04 Nov 2014 12:33:46 GMT

I have found an answer to this. A simple one.

Seeing that all the files will be in the same folder and the only thing that will be different in there names will be the "month at the end" i just removed this and replaced with *and it seemed to work.

Thanks for all your help!!

Re: Loading & Concatenate All Files from folder

MK_QSL — Tue, 04 Nov 2014 12:41:38 GMT

If you see our reply, we also have done the same.. Instead of Month, we have used *...