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.
For Each Ext in 'qvd'
For Each File in FileList ('C:\QlikView\QVD'&'\*.'&Ext)
FileName:
Load
*,
IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag
;
Load
'$(File)' as Name,
Date(Floor(Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD'))) as QVDNameDate
Autogenerate 1;
Let vFlag = Peek('Flag',0,'FileName');
Let vDate = Date(Date#(Peek('QVDNameDate',0,'FileName'),'DD/MM/YYYY'),'YYYYMMDD');
If('$(vFlag)' = 1) Then
Data:
LOAD
*
FROM
QVD\BACKLOG_$(vDate).qvd
(qvd);
ENDIF
Drop Table FileName;
Next File
Next Ext
When you say you are having every day backup as QVD... is there any date getting missed?
Why I am asking, because if no date is missing, you can simple create calendar and find out last date of every quarter using QuarterEnd function.
Hi Manish,
There is no missing date data in the QVD's as you asked. But suggest me the solution if some of the QVD Date is missing in the folder in anycase.
I can't even confirm how many years of data we have stored in the folder to loop. It has to fetch the data according tot he files which have been available in the folder itself
Cheers,
Chandra
Will definitely help you.
Let me know what exactly you want to achieve.
Only to find out QVD names having last date of every Quarter?
or you want to find out and load them as well.
Hi,
I have to findout those qvds and load all the qvds as one table (Concatenate)
So let's say example..
you want to find out...
BACKLOG_20150331
BACKLOG_20150630
BACKLOG_20150930
BACKLOG_20151231
BACKLOG_20160331
BACKLOG_20160630
BACKLOG_20160930
BACKLOG_20161231
and so on.. and want to load them and concatenate them..
What if we don't have QVD for last date?
i.e.BACKLOG_20160331.. means we don't have QVD for 31/03.. 30/03
But instead we have BACKLOG_20160329
Do you want to load 29/03 or not?
No i don't want to load QVDS which are not last date of the quarter. I don't want 29/03. If last date of qvd is not there just go for the available last date quarter qvds.
Thanks
For Each Ext in 'qvd'
For Each File in FileList ('C:\QlikView\QVD'&'\*.'&Ext)
FileName:
Load
*,
IF(QVDNameDate = Date(Floor(QuarterEnd(QVDNameDate))),1,0) as Flag
;
Load
'$(File)' as Name,
Date(Floor(Date#(TextBetween(SubField('$(File)','\',-1),'_','.'),'YYYYMMDD'))) as QVDNameDate
Autogenerate 1;
Let vFlag = Peek('Flag',0,'FileName');
Let vDate = Date(Date#(Peek('QVDNameDate',0,'FileName'),'DD/MM/YYYY'),'YYYYMMDD');
If('$(vFlag)' = 1) Then
Data:
LOAD
*
FROM
QVD\BACKLOG_$(vDate).qvd
(qvd);
ENDIF
Drop Table FileName;
Next File
Next Ext
Hi Manish,
Thanks for your help, I am trying to implement the same with changing the QVD folder path. I am getting the error below screenshot you can see. Variable vDate is not finding the value itseems. Can you please help me in the issue.
Cheers,
Satya Chandra
Can you upload your qvw script here?