Announcements
cancel
Showing results for
Did you mean:
Creator II

## Need Logic Help.

Hi,

Here I want the logic for Bucket with IntervelMatch.

7 Replies
Master III

IntervalMatch function

Partner - Specialist

Hi Lalit,

Warm Regards,
Susovan
Master III

Creator II

You could do this,

IntervalMatch(Rate)

RateFrom, RateTo, Rate Name

100, 100, 100-110

110, 120, 110-120

];

Or easiest use Class(Rate,10)

Class works in both script and expression/chart

I am not able to open QVW, can you show some sample data with expected output?

Contributor III

Hi,

Create excel like below:

 From To Bucket 100 110 100-110 111 120 110-120 121 130 120-130 131 140 130-140 141 150 140-150 150 1000 Out

Final:

Month,

Rate,

//      IF((Rate>=100 AND Rate<110),'100-110',

//         IF((Rate>=110 AND Rate<120),'110-120',

//             IF((Rate>=120 AND Rate<130),'120-130',

//                 IF((Rate>=130 AND Rate<140),'130-140',

//                     IF((Rate>=140 AND Rate<=150),'140-150',

//                         IF((Rate<100 AND Rate>150),'Out')))))) AS Bucket,

IF(ISNULL(Rate),0,1) AS Flag;

[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;

IntervalMatch(Rate)

To,

Bucket

FROM

(ooxml, embedded labels, table is Sheet1);

Creator II
Author

Hi,

Is there my option to do the same task without applying the join between item table and Date auto generate table.

***************************************************************************************

BucketTemp:

[

Min,Max,Bucket

100,110,100-110

111,120,110-120

121,130,120-130

131,140,130-140

141,150,140-150

];

Temp:

FROM

(ooxml, embedded labels, table is Sheet1);

MinMaxDate:

MAX(YearEnd([Wef Date])) AS MaxDate

FROM

(ooxml, embedded labels, table is Sheet1);

LET vMinDate =  PEEK('MinDate')-1;

LET vMaxDate =  PEEK('MaxDate');

JOIN (Temp)

AUTOGENERATE vMaxDate - vMinDate;

Temp1:

LOAD [Item Code] &'|'& Month AS Key,

[Item Code],

Month

RESIDENT Temp;

DROP TABLE Temp;

LEFT JOIN (Temp1)

[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:

Month,

Rate

WHERE Rate<>NULL();

//IF(ISNULL(Rate),0,1) AS Flag;

[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:

Min,

Max;

LEFT JOIN