Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel data load with restriction on language

Hello Experts,

I have a dataource as excel the output on the server displays with two Language packages 1. English  2. Chinese

and the excel has an update for every three months and stored back with last updated date on server.

Now my requirement is : Choose last three data updated excel files for only English language and load in my script for analysis.

any suggestions?  Thank you in advance

7 Replies
Mahamed_Qlik
Specialist
Specialist

Hi Sagar,

Could you please sample excel file with all the columns inside it ?

Regards,

Mahamed

Not applicable
Author

Hello Mahmmed,

Here is the sample screenshot of the sever folder. Inside I have different listed KPIs for different countries where their product ranking is changed every 3 months.

Data.PNG

Mahamed_Qlik
Specialist
Specialist

Dear Sagar,

You have sent the screenshot, I will need the actual excel file.

HirisH_V7
Master
Master

Hi ,

Can you post your excel file names!

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
jonathandienst
Partner - Champion III
Partner - Champion III

Use logic like:

Let vBaseDate = Date(MonthStart(Today(), -3), 'YYYY-MM');

For Each vFile in FileList('*_English.xlsx')

  Let vFileBase = SubField(SubField(vFile, '\', -1), '.', 1);

  Let vDate = Date#(Left(vFileBase, 6), 'YYYY-MM');

  If vDate >= vBaseDate Then

       LOAD *

       FROM [$(vFile)]

       (ooxml, embedded lables, table is Sheet1);

  End If

Next

Adjust the filepath inside FileList to point to the correct location for the files. Adjust the LOAD statement to your preferences.

EDIT - changed date comparison to >=

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
HirisH_V7
Master
Master

Hi

may be like this,

LET vFilePath = 'your path\latest Three excel files';

FOR EACH file in FileList('$(vFilePath)\Eng*.xlsx');

LET vFileYear =  Left(Right(file, 9), 4);  

LET vMaxYear1 = (Rangemax(vFileYear, vMaxYear1))-1;

LET vMaxYear2 = (Rangemax(vFileYear, vMaxYear2))-2;

LET vMaxYear  = (Rangemax(vFileYear, vMaxYear));

Next

Data:

LOAD *

FROM

[$(vFilePath)\Eng-$(vMaxYear1).xlsx]  

(ooxml, embedded labels, table is Data);

LOAD *

FROM

[$(vFilePath)\Eng-$(vMaxYear2).xlsx]  

(ooxml, embedded labels, table is Data);

LOAD *

FROM

[$(vFilePath)\Eng-$(vMaxYear).xlsx]  

(ooxml, embedded labels, table is Data);



By this you can pick last three years excel files based on the filename. we can do for filetime too using filetime()Function too.


PFA,

Hope this Helps,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
PradeepReddy
Specialist II
Specialist II

Try the Bellow script...

Place all your excel sheets in "D:\Qlik_201268" folder. I am attaching the Excel files and QVW files...

//Read the Filenames only

for each File in filelist('D:\Qlik_201268\*_ENGLISH.XLSX')

FileNames:

Load *inline

[

FileName

$(File)

];

next

//Sort the File names in Descending Order

Temp:

Load FileName,

RecNo()

resident FileNames

order by FileName Desc;

// Loads the Top 3 Files Data only

For i=0 to 2

Let vFileName=peek('FileName',$(i),'Temp');

Final_Data:

  Load *

  from $(vFileName) (ooxml, embedded labels, table is Sheet1);

next i

drop tables FileNames,Temp;