Hi,
Is there my option to do the same task without applying the join between item table and Date auto generate table.
***************************************************************************************
BucketTemp:
LOAD * INLINE
[
Min,Max,Bucket
100,110,100-110
111,120,110-120
121,130,120-130
131,140,130-140
141,150,140-150
];
Temp:
LOAD DISTINCT [Item Code]
FROM
(ooxml, embedded labels, table is Sheet1);
MinMaxDate:
LOAD MIN(YearStart([Wef Date])) AS MinDate,
MAX(YearEnd([Wef Date])) AS MaxDate
FROM
(ooxml, embedded labels, table is Sheet1);
LET vMinDate = PEEK('MinDate')-1;
LET vMaxDate = PEEK('MaxDate');
JOIN (Temp)
LOAD DISTINCT MONTH(Date) AS Month;
LOAD DATE(RECNO()+$(vMinDate)) AS Date
AUTOGENERATE vMaxDate - vMinDate;
Temp1:
LOAD [Item Code] &'|'& Month AS Key,
[Item Code],
Month
RESIDENT Temp;
DROP TABLE Temp;
LEFT JOIN (Temp1)
LOAD
[Item Code] &'|'& MONTH([Wef Date]) AS Key,
[Item Code],
Rate,
[Wef Date]
// MONTH([Wef Date]) AS MN
FROM
(ooxml, embedded labels, table is Sheet1);
Final:
LOAD [Item Code],
Month,
Rate
WHERE Rate<>NULL();
//IF(ISNULL(Rate),0,1) AS Flag;
LOAD
[Item Code],
Month,
IF( ISNULL( Rate ) AND PEEK([Item Code]) = [Item Code], PEEK( Rate ), Rate ) AS Rate
RESIDENT Temp1
ORDER BY [Item Code], Month;
DROP TABLE Temp1;
DROP TABLE MinMaxDate;
Data:
INTERVALMATCH(Rate) LOAD
Min,
Max;
LOAD * RESIDENT BucketTemp;
LEFT JOIN
LOAD *
RESIDENT BucketTemp;
DROP TABLE BucketTemp;
**********************************************************************************************************************
Thanks,
