topic Re: Need Logic Help. in QlikView

Need Logic Help.

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

Hi,

Please get the attached file.

Here I want the logic for Bucket with IntervelMatch.

Please help me out.

Re: Need Logic Help.

sujeetsingh — Wed, 08 Jun 2016 05:37:14 GMT

IntervalMatch function

Follow this thread

Re: Need Logic Help.

susovan — Wed, 08 Jun 2016 05:37:17 GMT

Hi Lalit,

Check this link,

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

Re: Need Logic Help.

sujeetsingh — Wed, 08 Jun 2016 05:54:48 GMT

Refer this sample please !

Re: Need Logic Help.

pho3nix90 — Wed, 08 Jun 2016 10:28:11 GMT

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

Re: Need Logic Help.

Kushal_Chawda — Wed, 08 Jun 2016 10:32:29 GMT

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

Re: Need Logic Help.

sangeethag — Wed, 08 Jun 2016 10:48:40 GMT

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

Re: Need Logic Help.

lalitkgehlot89 — Thu, 09 Jun 2016 09:41:18 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,