Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

How to load max(date) excel into qliksense

Hi Experts,

Can any one please help me on below requirement.

I have multiple excels in the subfolder like below.

extraction file.png

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

1 Solution

Accepted Solutions

Re: How to load max(date) excel into qliksense

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

7 Replies
Chanty4u
Esteemed Contributor III

Re: How to load max(date) excel into qliksense

did you check this code

Load the latest file of a folder

mahitham
Contributor

Re: How to load max(date) excel into qliksense

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
Esteemed Contributor III

Re: How to load max(date) excel into qliksense

try this

maxtable:

Load date(max(Date)) as MaxDate

from (qvd)

;

LET maxdate = peek('maxDate');

mahitham
Contributor

Re: How to load max(date) excel into qliksense

Hi stalwar1,

Could you please help me on this.

Re: How to load max(date) excel into qliksense

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

mahitham
Contributor

Re: How to load max(date) excel into qliksense

Hi stalwar1,

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

extraction file.png

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.

Re: How to load max(date) excel into qliksense

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

Community Browser