Dynamically load the max Jan file from a bunch of excel files

[suraj_qlikdev]

Hello swuehl‌, sunny‌,

Firstly thanks for all the help and support that you guys are providing on the qlik community. I regularly follow all your solutions you guys post to help other community members.

I would really appreciate if you could also help me with this issue/requirement.

I get one excel file every month from the business user and the file is named as YYYY MMM.

But I only want to load the latest Jan file for one of my dashboard.

Can you please help me with this requirement.

A sample code is greatly appreciated.

Note: I have attached some sample excel files.

Regards,

Suraj

[sunny_talwar]

May be this:

Temp:

LOAD Date#(Left(FileName(), 8), 'YYYY MMM') as Name

FROM

[* Jan.xlsx]

(ooxml, embedded labels, table is Data);

Max:

NoConcatenate

LOAD Date(Max(Name), 'YYYY MMM') as Name

Resident Temp;

DROP Table Temp;

LET vFile = Peek('Name');

Temp:

LOAD Car,

     Model,

     Cost,

     Country

FROM

[$(vFile).xlsx]

(ooxml, embedded labels, table is Data);

DROP Table Max;

[HirisH_V7]

Hi,

You can Load latest excel file based on file time()  like this,

Directory;

FOR Each File in filelist ('*.xlsx')

Main_Temp:
  load
  '$(File)' as Name,
FileTime( '$(File)' ) as FileTime

FROM
  '$(File)'
(ooxml, embedded labels, table is Data);

NEXT File;

Table:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Main_Temp
Order By FileTime asc;
drop table Main_Temp;

NoConcatenate

Excel:
load*,
  '$(File)' as Name,
FileTime( '$(File)' ) as FileTime

FROM
  '$(File)'
(ooxml, embedded labels, table is Data);
DROP Table Table;

There by we will get the latest excel file data,

Hope this helps,

PFA,

Hirish

[suraj_qlikdev]

Hello Sunny,

The code you sent is working fine when the source file and the solution (qvw) are in the same folder. But in real time that won't be the case right.

Application and source files would be in separate folders. 

Basically I'm having the issue when source excel file and the application are in separate folders.

I think the problem is with this part:

LET vFile = Peek('Name');

I'm okay even if you hard code the file address here.

Can you please help me with this?

Regards,

Suraj

[sunny_talwar]

You need to add whatever the relative path would be from the qvw to the source data file for this to work for you. If you can share your folder structure, I might be able to help you with the relative file structure.

[sunny_talwar]

For relative path, you can look here

Re: Relative path

[suraj_qlikdev]

Hi Sunny,

I'm aware of the relative path format. But I think here the issue is not with the relative path.

Even if you just create two folders and place the source files in one folder and the qvw in the other one the code is throwing an error. At the vFile=Peek ('Name') line the code is just going into the qvw folder.

Instead of Name can we give the source file location over there?

Regards,

Suraj

[sunny_talwar]

I understand that. You can add that part in the From Statement (since my guess is that all files would be sitting in the same folder)

Temp:

LOAD Car,

    Model,

    Cost,

    Country

FROM

[RelativePath\$(vFile).xlsx]

(ooxml, embedded labels, table is Data);

DROP Table Max;

[sunny_talwar · Accepted solution]

Check the zipped file out

[suraj_qlikdev]

Nope, source excel files are in one server and the application and qvd's are in another server.