Loading Multiple QVD's from 16 days with the QVD Naming Convention

[chandu441]

Hi All,

I have to import Last 16 days of Data from a Folder and Concatenate all the 16 qvds as one source in the dashboard.

Currently i am having 'N' of Qvds in the folder in that I have to take only SysDate - 16 Days of data dynamically every day when the application getting reload.

QVD naming convention you can see below Screenshot. I have to search the dates of last 16 days in the file name to load.

Please suggest me a solution which takes less time to load

[jonathandienst]

Simple solution that works well long as you don't have 100s of files matching the file name pattern:

BackLog:

LOAD 0 as Dummy AutoGenerate 0;

Let zToday = Today();

For Each vFile in FileList('<your path>\BACKLOG_*.qvd')

  Let zFileDate = Date#(Right(SubField(vFile, '.', 1)), 'yyyyMMdd';

  If zFileDate > zToday - 16 Then

  Concatenate(BackLog)

  LOAD *,

      FileBaseName() as Source

  From [$(vFile)] (qvd);

  End If

Next

[jonathandienst · Accepted solution]

Or this one loads the most recent 16 files and will work well even if there are 1000s of files maching the file pattern:

BackLog:

LOAD 0 as Dummy AutoGenerate 0;

Let zToday = Today();

Let zIndex = 1;

Let zCount = 0;

Do

  Let zFileDate = Date(zToday - zIndex, 'yyyyMMdd');

  Let vFile = '<your path>\BACKLOG_$(zFileDate).qvd';

  If zFileDate > zToday - 16 Then

     If Alt(QvdNoOfRecords(vFile), 0) > 0 Then

        Concatenate(BackLog)

        LOAD *,

            FileBaseName() as Source

        From [$(vFile)] (qvd);

        Let zCount = zCount + 1

     End If

  End If

  Let zIndex = zIndex + 1;

Loop Until zCount = 16

[chandu441]

Hi Jonathan,

Semicolon missed in the line

Let zCount = zCount + 1

I corrected that and trying to load the data now let me post the solution is worked or not in my next reply

Thanks

[jonathandienst]

>>Semicolon missed in the line

That's always a risk with script quickly cobbled together in Notepad++

[chandu441]

Hi Jonathan,

Thanks for your help....

Now the User got change the requirement slightly.

User wants me to load the data only for Last date of Previous Quarters in that over all QVD's folder.

Could you please help me Jonathan

Thanks in Advance

[rahulpawarb]

Hello Chandra,

You can refer below expression to get the Last Date of Previous Quarter:

=Date((QuarterStart(Today())-1), 'DD-MM-YYYY')

If today's date is 24-12-2016 then the last date of previous quarter will be 30-09-2016.

Hope this will help.

Thank you!

Rahul

[chandu441]

Thanks Rahul,

Let me use the expression and post the result.

Cheers

Chandra

[rahulpawarb]

Welcome

[chandu441]

Code which Worked for my scenario. Little change of Jonathan Code here

Let zToday = Today();

Let zIndex = 1;

Let zCount = 0;

Do

  Let zFileDate = Date(zToday - zIndex, 'yyyyMMdd');

  Let vFile = 'E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\Backlog_History\BACKLOG_$(zFileDate).qvd';

  If zFileDate >= zToday - 16 Then

     If Alt(QvdNoOfRecords(vFile), 0) > 0 Then

        Concatenate(BackLog_LastDayofQuarter)

        LOAD *,

            FileBaseName() as QVD_Name

        From [$(vFile)] (qvd);

        Let zCount = zCount + 1;

     End If

  End If

  Let zIndex = zIndex + 1;

Loop Until zCount = 16