Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Not applicable

Loading & Concatenate All Files from folder

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

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Loading & Concatenate All Files from folder

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!!

View solution in original post

9 Replies
Highlighted

Re: Loading & Concatenate All Files from folder

 

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

Highlighted
MVP
MVP

Re: Loading & Concatenate All Files from folder

LOAD

           *

FROM

[IRL 1.1 *.xlsx]

(ooxml, embedded labels, table is Sheet1)

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

Highlighted
Not applicable

Re: Loading & Concatenate All Files from folder

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

Highlighted
MVP
MVP

Re: Loading & Concatenate All Files from folder

for each file in FileList(path_Pallet)

IRL1:

load*

From

(ooxml, embedded labels, table is Sheet1);

next file

Highlighted
Not applicable

Re: Loading & Concatenate All Files from folder

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

Highlighted
MVP
MVP

Re: Loading & Concatenate All Files from folder

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

Highlighted
Not applicable

Re: Loading & Concatenate All Files from folder

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

Highlighted
Not applicable

Re: Loading & Concatenate All Files from folder

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!!

View solution in original post

Highlighted
MVP
MVP

Re: Loading & Concatenate All Files from folder

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