Dynamic loading of excels files into Qlikview

[s10157754]

Dear Qlikview Experts,

I had been stuck at this part for a couple of days and I hope you all could help me solve this problem. I want my Qlikview to loop through every excel files that contains 'AB' in the file name in a folder and import into Qlikview. There are multiple sheets in each excel file. However, I only want to import sheets "AB_100" and "AB_200" only (If able to create field based on sheet name would be great! Just create the field based on number will do).

The most challenging part is pulling the data from each sheet. As the file name contains the date information. I would only like to import the data 1 day before the file date. For example: In my 01-01-2018AB excel file, every sheet contains data range from 12/31/2017 (00:00:00) ~ 1/1/2018 (06:00:00). So I only want to import the data for 12/31/2017 if the file date is 01-01-2018AB. (E.g. Import the data for 1/1/2018 if the file date is 01-02-2018AB; Import the data for 1/2/2018 if the file date is 01-03-2018AB    etc...)

I had attached the zip file below for your reference. I am not good at scripting. So any ideas from you all would be greatly appreciated! Thanks a lot in advance!

[tamilarasu]

Hi Zhang,

Here you go.

Set vPath = 'C:\Users\QianNing\Desktop\GE';  //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

If Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY') >= '$(LoadDate)' then

//for each Sheet in 'BC_227$', 'BC_226$'

for each Sheet in 'BC_227', 'BC_226'

Data:
LOAD @1 as A,
@2 as B,
@3 as C,
SubField('$(Sheet)','_',2) as SheetName,
FileBaseName() as FileName,
Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY') as FileDate
FROM
[$(File)]
(biff, no labels, table is $(Sheet)$) Where  Date#(left(FileBaseName(),10),'MM-DD-YYYY')>@1;

Next

ENDIF

NEXT

[s10157754]

Dear Tamil,

Thanks for the information. It works like a charm!

Best Regards

QianNing

[s10157754]

Dear Tamil,

Sorry for keep disturbing you, if I would like to extract the date field into 3 individual fields as Year, Month and Day, Is it possible to achieve in scripting?

Best Regards

QianNing

[pradosh_thakur]

Set vPath = 'C:\Users\QianNing\Desktop\GE';  //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

If Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY') >= '$(LoadDate)' then

//for each Sheet in 'BC_227$', 'BC_226$'

for each Sheet in 'BC_227', 'BC_226'

Data:
LOAD @1 as A,
@2 as B,
@3 as C,
SubField('$(Sheet)','_',2) as SheetName,
FileBaseName() as FileName,
Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY') as FileDate,

year(Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY')) as Year,

month(Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY')) as Month,

day(Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY')) as Day
FROM
[$(File)]
(biff, no labels, table is $(Sheet)$) Where  Date#(left(FileBaseName(),10),'MM-DD-YYYY')>@1;

Next

ENDIF

NEXT

[s10157754]

Dear Pradosh,

Thanks for your prompt reply. As my file date will be one day earlier than the data date. So I would only like to divide my date field into 3 individual fields as Year, Month and Day respectively. Thanks for your help in advance!

[tamilarasu]

Hi Zhang,

Here is the updated code.

Set vPath = 'C:\Users\QianNing\Desktop\GE';  //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

Let vFileDate = Date(Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY'),'MM-DD-YYYY');

If '$(vFileDate)' >= '$(LoadDate)' then

for each Sheet in 'BC_227', 'BC_226'

Data:
LOAD @1,
@2,
@3,
SubField('$(Sheet)','_',2)   as SheetName,
FileBaseName()               as FileName,
'$(vFileDate)'               as FileDate,
Day(Date#('$(vFileDate)','MM-DD-YYYY'))            as Day,
Month(Date#('$(vFileDate)','MM-DD-YYYY'))        as Month,
Year(Date#('$(vFileDate)','MM-DD-YYYY'))          as Year
FROM
[$(File)]
(biff, explicit labels, table is $(Sheet)$) Where  Date#('$(vFileDate)','MM-DD-YYYY') > @1;

Next
ENDIF

Next

[s10157754]

Dear Tamil,

My apologies for the late reply.

I can see that your updated code is extracting date field based on my filename. However, since my filename date is 1 day faster than my data date that has been loaded into Qlikview. So when creating list box for Year, Month and Day, it will be not referring to the same date that has been selected by the user. You can see from the dialog box below.

So my main objective will be: Is it possible for us just to split date field based on the data date that has been loaded into Qlikview as Year, Month and Day? (Where the blue color arrow is pointing)

Without using the filename date, as fieldname date will not be the same as the data date that has been loaded into Qlikview.

Best Regards

QianNing

[tamilarasu]

Hi QianNing,

Please check the below script.

Set vPath = 'C:\Users\QianNing\Desktop\GE'; //Change your path here

Let LoadDate = Date(Date#('01-01-2018','MM-DD-YYYY'),'MM-DD-YYYY');  //Change your date here

for each File in FileList(vPath & '\*GE*.xls')

Let vFileDate = Date(Date#(Left(SubField('$(File)','\',-1),10),'MM-DD-YYYY'),'MM-DD-YYYY');

If '$(vFileDate)' >= '$(LoadDate)' then

for each Sheet in 'BC_227', 'BC_226'

Data:
LOAD @1,
@2,
@3,
SubField('$(Sheet)','_',2)  as SheetName,
FileBaseName()              as FileName,
'$(vFileDate)'              as FileDate,
Day(@1)            as Day,
Month(@1)        as Month,
Year(@1)          as Year
FROM
[$(File)]
(biff, explicit labels, table is $(Sheet)$) Where  Date#('$(vFileDate)','MM-DD-YYYY') > @1;

Next
ENDIF

Next

Kind Note: I would suggest you to understand the code because you can learn QlikView and do these kind of changes by yourself. (Main thing is you don't need to wait for us to help you and also you can help others in future )

[s10157754]

Dear Tamil,

Thanks a ton for your help!

I am not good at scripting but I will try my best to learn the coding in Qlikview.

Best Regards

QianNing

[tamilarasu]

Great. Please do not hesitate to let us know if you have any questions or concerns. Have a nice day QianNing.