How to load max(date) excel into qliksense

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

I have multiple excels in the subfolder like below.

daily they will add one excel. From all these excels I have to load only latest excel means with max date excel need to load dynamically.

For example in the above all excels "Extract20180616" is the latest excel this one need to load if any other excel Extract20180617 is added then it will be the latest so Extract20180617  has to be load.

Please help me on this.

Thanks in advance

[Chanty4u]

did you check this code

Load the latest file of a folder

[mahitham]

Hi Chanty,

Thanks for your reply.

Actually I have to load the file based on date in the filename not by file time its manual process.

I am looking for the solution in qliksense.

something like below . Please help me to pass max(date) instead of today()

LET vToday = date(today(), 'DDMMYYYY');

LOAD *

FROM $(vToday)MyFile.xls;

[Chanty4u]

try this

maxtable:

Load date(max(Date)) as MaxDate

from (qvd)

;

LET maxdate = peek('maxDate');

[mahitham]

Hi stalwar1,

Could you please help me on this.

[sunny_talwar]

mahitham‌ what you show in the image are folders, right? you have a new folder or a new Excel file each day?

[mahitham]

Hi stalwar1,

Sorry the Extract20180612 are the csv files in server.I have prepared sample structure in local desktop.

Business will update only 2columns in the csv file when changes required and they will place the latest one in the Extractions folder. Its not the daily process its depends on business change.

In the script based on latest file means max date file need to load dynamically. Please help me on this.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try like this

SUB DoDir (Root)

FOR Each File in FileList(Root&'\*.CSV')

Table:

        LOAD '$(File)' as Name,

            Date#(Right(SubField(SubField('$(File)', '/', -1), '.', 1), 8), 'YYYYMMDD') as Date

        AutoGenerate 1;

NEXT File

    FOR Each Dir in DirList(Root&'\*' )

      CALL DoDir (Dir)

NEXT Dir

END SUB

CALL DoDir ('lib://MyData')

Max:

LOAD Date(Max(Date), 'YYYYMMDD') as MaxDate

Resident Table;

vMax = Peek('MaxDate');

DROP Table Max, Table;

FinalTable:

LOAD

    Dim,

    Value

FROM [lib://MyData/Extract$(vMax).csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);