Need Logic Help.

[lalitkgehlot89]

Hi,

Please get the attached file.

Here I want the logic for Bucket with IntervelMatch.

Please help me out.

[sujeetsingh]

IntervalMatch function

Follow this thread

[susovan]

Hi Lalit,

Check this link,

Link : https://community.qlik.com/thread/119623

[sujeetsingh]

Refer this sample please !

[pho3nix90]

You could do this,

IntervalMatch(Rate)

LOAD * INLINE [

    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

[Kushal_Chawda]

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

[sangeethag]

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

Change your script like below

Final:

LOAD [Item Code],

     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;

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;

IntervalMatch(Rate)

LOAD From,

     To,

     Bucket

FROM

(ooxml, embedded labels, table is Sheet1);

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