Need Logic help.

[lalitkgehlot89]

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,