topic Loading data from diffrent folder at a time in QlikView

Loading data from diffrent folder at a time

Thu, 09 Jun 2016 06:01:06 GMT

my data are in this location i have to load all the date at a time

[..\Data Source\28-Jan-2016\NT\Change_in_Book_and_Market_Value_28-01-16.XLS]

[..\Data Source\29-Jan-2016\NT\Change_in_Book_and_Market_Value_29-01-16.XLS]

[..\Data Source\01-Feb-2016\NT\Change_in_Book_and_Market_Value_01-02-16.XLS]

[..\Data Source\02-Feb-2016\NT\Change_in_Book_and_Market_Value_02-02-16.XLS]

can u hlep me please

Re: Loading data from diffrent folder at a time

senpradip007 — Thu, 09 Jun 2016 06:06:45 GMT

Could you please share the script?

Re: Loading data from diffrent folder at a time

bindu_apte — Thu, 09 Jun 2016 06:10:17 GMT

Hi,

If you wanted load all these excels at a time then please create a folder and from there you can just load all excels as per name.

ie., something like this.

[\Data Source\NT\Change_in_Book_and_Market_Value_*.xls

Re: Loading data from diffrent folder at a time

Thu, 09 Jun 2016 06:14:06 GMT

pradip to be frank i cannot share the script and i have a folder called data source in it the files are sepreated by date as folder name. inside Date folder i have a folder called NT in all the folders.Inside the NT folder i have my file which i have to load at a time

Re: Loading data from diffrent folder at a time

senpradip007 — Thu, 09 Jun 2016 07:22:02 GMT

Try like

for i = num(date#(vStartDate, 'DD-MM-YYYY')) to num(date#(vEndDate, 'DD-MM-YYYY'))

let vTemp = text(date($(i), 'DD-MMM-YYYY'));

LOAD

ID,

Type,

Data

FROM

[..\Data Source\$(vTemp)\NT\*.XLS]

(ooxml, embedded labels, table is Sheet1);

As your forder names consist of date , take input of Minimum Date and Maximum Date from front end. Here vStartDate consists of Mininum Date and vEndDate consists of Maximum Date.

Re: Loading data from diffrent folder at a time

Thu, 09 Jun 2016 07:41:56 GMT

pradip thanks for your help man but i have doubt if i assign start date and end date there might be even saturday and sunday in middle it might give an error

Re: Loading data from diffrent folder at a time

jonathandienst — Thu, 09 Jun 2016 07:51:16 GMT

This perhaps:

For Each vDir in DirList('..\Data Source\*')

LOAD *,

FileBaseName() as Source

FROM $(vDir)\NT\*.xls

(biff, embedded labels, table is Sheet1);

Next

Assumes that all the files have the same fields. Adapt the dirlist path and the load statement for your requirements.

Re: Loading data from diffrent folder at a time

Thu, 09 Jun 2016 08:03:52 GMT

but i have the folder in daily date from 1-01-2016 to 28-02-2016 except weekends i have to retrive the file on the date

28-01-2016,

29-01-2016,

01-02-2016,

02-02-2016

Re: Loading data from diffrent folder at a time

senpradip007 — Thu, 09 Jun 2016 09:07:41 GMT

Try to use below script.

for i = num(date#(vStartDate, 'DD-MM-YYYY')) to vEndDate

let vTemp = text(date($(i), 'DD-MMM-YYYY'));

if not (WeekDay(date($(i))) = 'Sat' or WeekDay(date($(i))) = 'Sun') then

LOAD

ID,

Type,

Data

FROM

[..\Data Source\$(vTemp)\NT\*.XLS]

(ooxml, embedded labels, table is Sheet1);

ENDIF

Re: Loading data from diffrent folder at a time

Anonymous — Thu, 09 Jun 2016 10:29:12 GMT

Hi Naveen,

Look at this: Loading data from multiple xls-files from folder with subfolder

Regards!

Re: Loading data from diffrent folder at a time

Thu, 09 Jun 2016 11:02:28 GMT

thank you manuel capella it was helpfull

Re: Loading data from diffrent folder at a time

Anonymous — Thu, 09 Jun 2016 11:05:52 GMT

Your wellcome.

Re: Loading data from diffrent folder at a time

Thu, 09 Jun 2016 12:12:26 GMT

thanks pradip for great idea

Re: Loading data from diffrent folder at a time

senpradip007 — Fri, 10 Jun 2016 06:12:53 GMT

Best of luck!!!