Need Logic help.

lalitkgehlot89 — Wed, 25 Nov 2020 16:16:04 GMT

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,

topic Need Logic help. in QlikView

Need Logic help.