Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have to import Previous Quarter Last Day (Date) Data from a QVD's Folder which is having every day backup QVD's and Concatenate all the qvds as one source in the dashboard.
Currently i am having 'N' of Qvds in the folder in that I have to take only Last Date of all the Previous Quarters data dynamically every day when the application getting reload.
QVD naming convention you can see below Screenshot. In the Folder I have to search the dates of last day of every quarter in the file name to load.
Please suggest me a solution which takes less time to load.
Hi Manish,
Little I have changed the Code. Just removed Data Functions in the code. Then VDate Value is getting worked for me.
Here is the below changed code for Reference.
Thanks Manish
Hi Manish,
I ran your script as screenshot shared in the earlier reply. But the thing is it is executing long time when i run every time. I am not getting the concatenate and output as well. I am using only 22 QVD's for Testing purpose as of now.
Here is the code i am sending for you reference.
For Each Ext in 'qvd'
For Each File in FileList ('E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\PE DEV\Backlog_History'&'\*.'&Ext)
FileName:
Load
*,
IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag
;
Load
'$(File)' as Name,
Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD') as QVDNameDate
Autogenerate 1;
Let vFlag = Peek('Flag',0,'FileName');
Let vDate = Peek('QVDNameDate',0,'FileName');
If('$(vFlag)' = 1) Then
BackLog:
LOAD *
FROM
E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\PE DEV\Backlog_History\BACKLOG_$(vDate).qvd
(qvd);
ENDIF
Drop Table FileName;
Next File
Next Ext
Little Change of the Code is just concatenating the table with 0 as dummy otherwise it will create synthetic keys by loading same kind of column table repeat.
Worked code for me is here.
For Each Ext in 'qvd'
For Each File in FileList ('E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\Backlog_History'&'\*.'&Ext)
BackLog_LastDayofQuarter:
LOAD 0 as DummyLoad AutoGenerate 0;
FileName:
Load
*,
IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag
;
Load
'$(File)' as Name,
Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD') as QVDNameDate
Autogenerate 1;
Let vFlag = Peek('Flag',0,'FileName');
Let vDate = Peek('QVDNameDate',0,'FileName');
If('$(vFlag)' = 1) Then
Concatenate(BackLog_LastDayofQuarter)
LOAD * ,
FileBaseName() as QVD_Name
FROM
E:\QLIKVIEW MAIN\Qlikview Source Documents\PLR\Backlog_History\BACKLOG_$(vDate).qvd
(qvd);
ENDIF
Drop Table FileName;
Next File
Next Ext