topic Re. :Re: load excel files from different folders in QlikView

load excel files from different folders

Wed, 06 Apr 2011 09:51:11 GMT

hello all.

I have monthly invoice tables in different folders. like this path.

\invoice\2010\

\invoice\2011\

Now i need to load last 3 months invoice tables from 2010 and load all tables from 2011.

How to do this. Any suggest?

Regards. Muncho

Re. :load excel files from different folders

vincent_ardiet — Wed, 06 Apr 2011 10:02:07 GMT

Hi,

Can you give us the pattern used to named your files ?

And what is the rule ? Always full current year and 3 last months of past year ?

Regards,

Vincent

load excel files from different folders

Wed, 06 Apr 2011 10:09:51 GMT

Hello Muncho,
you can use a for-each loop, something like this:

for each i in 2010, 2011 set yr=$(i); invoiceTable: load /*columns: */ @1, '$(yr)' AS TheYear from [\invoice\$(yr)\invoice*.xls] (biff, no labels, table is Sheet1$); next ;

Best regards

load excel files from different folders

Wed, 06 Apr 2011 10:19:30 GMT

Hi Dear,

Load *,

Date(MonthStart(Date,3),'MMM-YYYY') as MonthYear

from table;

join

load
*,

Date(MonthStart(Date,3),'MMM-YYYY') as MonthYear

from table where MonthYear>'Sep-2010';

hope this is useful..

Regards

Re. :load excel files from different folders

Wed, 06 Apr 2011 11:58:54 GMT

Hi Vincent.

Invoice file names pattern is like: [monthfullname year.xls]

I need last 6 months invoices. So 3 months from current year, 3 months from last year.

load excel files from different folders

Wed, 06 Apr 2011 12:37:36 GMT

Hello Agger.

Thanks for this code. But i need last 6 months invoices. How do i do in your code?

Re. :Re: load excel files from different folders

vincent_ardiet — Wed, 06 Apr 2011 14:58:56 GMT

Hi Muncho,

You can use a code like this one :

SET MonthNames='January;February;March;April;May;Jun;July;August;September;October;November;December';

for i = -5 to 0

let filename = year(addmonths(today(),i)) & '\' & month(addmonths(today(),i)) & ' ' & year(addmonths(today(),i)) ;

load @1,@2 from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

next

If you don't want the current month just put -6 and -1 if the for range.

Regards,

Vincent

Re. :Re: load excel files from different folders

Wed, 06 Apr 2011 15:12:02 GMT

Hi Vincent

thank you very much. Your the best 😄

Re. :Re: load excel files from different folders

Thu, 07 Apr 2011 08:40:48 GMT

Hello Vincent

I need to ask you one more thing. Now i need to join or concatenate them. Also make month field from month in file name .

How to do this?

Re. :Re: Re. :Re: load excel files from different folders

vincent_ardiet — Thu, 07 Apr 2011 09:07:14 GMT

Hi,

Juste add a new field in the load statement like this one :

load month(addmonths(today(),$(i))) as monthfield, @1,@2 from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

Regards,

Vincent

Re. :Re: Re. :Re: load excel files from different folders

Thu, 07 Apr 2011 09:17:58 GMT

Thanks for your fast replay.

And how about join them?

Re. :Re: Re. :Re: Re. :Re: load excel files from different folders

vincent_ardiet — Thu, 07 Apr 2011 09:22:47 GMT

Do you mean join or concatenate ?

If your excel files have the same structure (and it seems to be the case), all those LOAD statements will by concatenated.

Regards,

Vincent

Re. :Re: Re. :Re: Re. :Re: load excel files from different folders

Thu, 07 Apr 2011 09:34:57 GMT

yepp it will concatenate. But it's not. Because my tables structure is not same. That's why im asking here. If it was same, tables must concatenate and no need to ask from here right.

Re. :Re: Re. :Re: Re. :Re: Re. :Re: load excel files from different folders

vincent_ardiet — Thu, 07 Apr 2011 09:42:46 GMT

Try with this:

SET MonthNames='January;February;March;April;May;Jun;July;August;September;October;November;December';

set firstfile=1;

for i = -5 to 0

let filename = year(addmonths(today(),i)) & '\' & month(addmonths(today(),i)) & ' ' & year(addmonths(today(),i)) ;

if firstfile=1 then

load month(addmonths(today(),$(i))) as month, * from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

set firstfile=0 ;

else

concatenate load month(addmonths(today(),$(i))) as month, * from $(filename).csv (txt, codepage is 1252, no labels, delimiter is ';', msq);

endif

Regards,

Vincent

Re. :Re: Re. :Re: Re. :Re: Re. :Re: load excel files from different folders

Thu, 07 Apr 2011 10:41:43 GMT

Really thank you Vincent.

Re: Re. :load excel files from different folders

stantrolav — Fri, 01 Nov 2013 07:48:47 GMT

There is a system varible named LongMonthNames. It have all full names of months. You can get month name by

date(today, 'MMMM')

and set you full month name by adding

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';