Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Need Logic Help.

Hi,

Please get the attached file.

Here I want the logic for Bucket with IntervelMatch.

Please help me out.

7 Replies
Highlighted
Master III
Master III

Re: Need Logic Help.

IntervalMatch function

Follow this thread

Highlighted
Partner
Partner

Re: Need Logic Help.

Hi Lalit,

Check this link,

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

Highlighted
Master III
Master III

Re: Need Logic Help.

Refer this sample please !

Highlighted
Creator II
Creator II

Re: Need Logic Help.

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

Highlighted
MVP
MVP

Re: Need Logic Help.

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

Highlighted
Contributor III
Contributor III

Re: Need Logic Help.

Hi,

Create excel like below:

 

FromToBucket
100110100-110
111120110-120
121130120-130
131140130-140
141150140-150
1501000Out

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

Highlighted
Creator II
Creator II

Re: Need Logic Help.

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,