Need to read Latest reload and second last reload of QVD file

[VenkateshYarra]

Hello All,

I have a excel file which contains with two fields ( QVD Name and QVD Path )

I need to read the path of the qvd from excel file of particular QVD and need to get the details of the qvd like when it is reloaded and what is the second reload time  and name of the qvd file and finally need to store data into .csv format using delimiter as '|'. This is my requirement.

Now i will tell you what i did till now.

I can get the qvd details from particular folder but not able to get it from excel file.

The below is the script i am using to get it. Please correct me if I am wrong.

=========================================================

let vLastDateTime = '$(vDateTime)';

for each vFile in FileList('$(vInVitroPath)Quote_items.qvd')

InVivo:
LOAD

'$(vFile)' as filename,
'InVivo '& TableName as QVDName,
QvdCreateTime('$(vFile)') as qvdcreatedtime,
'$(vLastDateTime)' as qvdlastcreatedtime

FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]);

Let vDateTime = QvdCreateTime('$(vFile)');

QVDCSV:
Load
QVDName,
qvdcreatedtime,
qvdlastcreatedtime
Resident InVivo;

Drop table InVivo;

STORE QVDCSV into $(vPath)QVDCSV.csv(txt,delimiter is '|');
next;

 =============================================================

The above one is for get data from folder where qvd resides.

But i need to read the path of the qvd from excel file.

In excel file i have like below

NameoftheQVD               Path

Test.qvd                              ../../xyz/abc/Test.qvd

Test1.qvd                           ../../pqr/efg/Test1.qvd

(...)

So on....

Can any help to get this result.

Thank you in advance

Regards

Venkatesh

[VenkateshYarra]

Hello All,

I am struck in between...

I got the solution to some extend, that is nothing but, fetching the qvd details from the excel file where the qvd name and path is defined. Like below table qvd excel details

Name	Path
QVD Quotes	H:\QVD\QVD_Quotes.qvd
New1 Pricing	H:\QVD\New1\QVD_Pricing.qvd

Below is the script :

QVDDetails:
LOAD Name,
Path
FROM
QVDDetails.xlsx
(ooxml, embedded labels, table is Sheet1);

let noRows = NoOfRows('QVDDetails')-1;

for i=0 to $(noRows)

let vVar = peek('Path',$(i),'QVDDetails');
let vName = peek('Name',$(i),'QVDDetails');

for each vFile in FileList('$(vVar)');
let vFileName = mid(vFile, index(vFile, '\', -1) + 1, 99);

InVivo:
LOAD
'$(vFile)' as filename,
'$(vFileName)' as [File Name],
'$(vName)' AS Name,
TableName as QVDName,
QvdCreateTime('$(vFile)') as qvdcreatedtime
FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]);

next ;
next i;

QVDCSV:
Load
QVDName,
Left( [File Name], Len( Trim( [File Name]) ) - 4) AS [File Name],
Name AS Name2,
qvdcreatedtime
//qvdlastcreatedtime
Resident InVivo;

STORE QVDCSV into $(vPath)Data_Listener.csv(txt,delimiter is '|');

Drop Table InVivo;

=================================================================

Additional to this. what i need is...

When qvd is not refreshed then it has to store old qvdcreatedtime only. If QVD had new modified date and time in meta data then it has to load new modified date and time.

For example:

QVD quotes has reload time as  22-01-2020 01:57:11 

When i run the above script then it has to load only 22-01-2020 01:57:11 for first time,

For second time,

Now QVD quotes has new reload time as 22-01-2020 03:45:22

then, when i run the above script then it has to show me two reload time ie.,

Latest reload time as 22-01-2020 03:45:22 and 

Previous reload time as 22-01-2020 01:57:11.

so on.. 

Output should look like below.

I just want one CSV file which will contain both previous refresh time and the current refresh time

For example the CSV should contain the below format:
QVDName|CurrentRefreshTime|PreviousRefreshTime
1stQVD|22-01-2020 02:45:23|22-12-2019 02:20:14
2ndQVD|20-01-2020 09:12:02|13-01-2020 09:16:15
3rdQVD|21-01-2020 10:35:33|22-01-2020 10:20:23

So far my logic has been working (i.e. i get the CurrentRefreshTime and PreviousRefreshTime whenever i run the Qlikview application). But if the QVD has not been modified/Unchanged then my PreviousRefreshTime gets overwritten by the CurrentRefreshTime. Ideally the timestamps in the file name should remain unchanged if the QVD is not modified.(Refresh Time unchanged)

Thank you in advance

Regards

Venkatesh

[VenkateshYarra · Accepted solution]

Now it is working exactly what i need. Below is the script. 

QVDDetails:
LOAD Name,
Path
FROM
QVDDetails.xlsx
(ooxml, embedded labels, table is Sheet1);

let noRows = NoOfRows('QVDDetails')-1;

for i=0 to $(noRows)

let vVar = peek('Path',$(i),'QVDDetails');
let vName = peek('Name',$(i),'QVDDetails');

for each vFile in FileList('$(vVar)');
LET vDateTimes$(i) = QvdCreateTime('$(vFile)');
let vQVDLastCreatedTime$(i) = IF('$(vDateTimes$(i))' = '$(vDateTime$(i))','$(vQVDLastCreatedTime$(i))','$(vDateTime$(i))');

InVivo:
LOAD
'$(vFile)' as filename,
'$(vName)' AS Name,
QvdCreateTime('$(vFile)') as qvdcreatedtime,
'$(vQVDLastCreatedTime$(i))' as qvdlastcreatedtime
FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader])
;

LET vDateTime$(i) = IF('$(vDateTimes$(i))' <> '$(vQVDLastCreatedTime$(i))','$(vDateTimes$(i))','$(vQVDLastCreatedTime$(i))');

next ;
next i;

NoConcatenate
QVDCSV:
Load
Name AS QVDName,
qvdcreatedtime,
qvdlastcreatedtime
Resident InVivo;

STORE QVDCSV into $(vPath)Data_Listener.csv(txt,delimiter is '|');

Drop Table InVivo;

Regards

Venkatesh