topic Re: Excel data load with restriction on language in QlikView

Excel data load with restriction on language

Mon, 25 Jan 2016 12:39:33 GMT

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

Re: Excel data load with restriction on language

Mahamed_Qlik — Mon, 25 Jan 2016 12:59:20 GMT

Hi Sagar,

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

Regards,

Mahamed

Re: Excel data load with restriction on language

Mon, 25 Jan 2016 15:13:36 GMT

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.

Re: Excel data load with restriction on language

Mahamed_Qlik — Wed, 27 Jan 2016 09:01:47 GMT

Dear Sagar,

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

Re: Excel data load with restriction on language

HirisH_V7 — Wed, 27 Jan 2016 09:06:57 GMT

Hi ,

Can you post your excel file names!

-Hirish

Re: Excel data load with restriction on language

jonathandienst — Wed, 27 Jan 2016 09:33:58 GMT

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 >=

Re: Excel data load with restriction on language

HirisH_V7 — Wed, 27 Jan 2016 10:29:52 GMT

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

Re: Excel data load with restriction on language

PradeepReddy — Wed, 27 Jan 2016 11:02:24 GMT

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)

];

//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;