Hello Dada,
I could see the attachments. However, I have re-attached it herewith.
Also, refer below given modified script:
DateListTemp:
LOAD
Distinct LEFT(RIGHT(Date([Transaction date],'DD/MM/YYYY'),7),2)& RIGHT(Date([Transaction date],'DD/MM/YYYY'),4) AS MonthYear
FROM [Copy of incree load data.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
DateList:
LOAD
MonthYear
Resident DateListTemp
ORDER BY 1;
Drop Table DateListTemp;
//EXIT SCRIPT;
FOR i=0 TO NoOfRows('DateList')-1
LET vMonthYear = PEEK('MonthYear', $(i), 'DateList');
LET vQVDPath = 'QVDs\'; // Set QVD Storage Directory
Sales:
LOAD
Region,
id,
product,
sales,
[Transaction date]
FROM [Copy of incree load data.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where LEFT(RIGHT(Date([Transaction date],'DD/MM/YYYY'),7),2)& RIGHT(Date([Transaction date],'DD/MM/YYYY'),4) = $(vMonthYear);
// Check to see if this is the first reload. if it is, skip this step
if not isnull(QVDCreateTime('$(vQVDPath)Sales_$(vMonthYear).qvd')) THEN
Concatenate(Sales)
LOAD *
FROM $(vQVDPath)Sales_$(vMonthYear).qvd(qvd)
WHERE Not Exists(id);
end if
// If data exists within table, store to QVD.
if NoOfRows('Sales') > 0 THEN
STORE Sales INTO $(vQVDPath)Sales_$(vMonthYear).qvd;
Drop Table Sales;
end if
Next
Drop Table DateList;
Thank you!
Rahul
