Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator II
Creator II

Hi Sagar,

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

Regards,

Mahamed

Highlighted
Not applicable

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

Highlighted
Creator II
Creator II

Dear Sagar,

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

Highlighted
Master
Master

Hi ,

Can you post your excel file names!

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted

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
Highlighted
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!”
Highlighted
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;