topic interval match in QlikView

interval match

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

Hi people,

I have two tables,

table1:

Load * Inline [

Product,Quantity_Sold

A,5

A,10

A,5

A,12

A,8

B,5

B,10]

Table2:

load * Inline [

Product,Start,End,Price

A,1,3,100

A,4,15,110

A,16,30,120

A,31,40,150

B,1,3,160

I'am using below script to create a match

Left join(Table1)

Intervalmatch(Quantity_Sold)

LOAD Start, End,Product

RESIDENT Table2;

drop table Table2;

my sample output is like

Product Quantity_Sold Price

A 5 520

A 10 1100

A 5 600

so on.

Re: interval match

sunny_talwar — Mon, 20 Nov 2017 12:47:49 GMT

May be you need extended IntervalMatch here

Left join(Table1)

Intervalmatch(Quantity_Sold, Product)

LOAD Start,

End,

Product

RESIDENT Table2;

drop table Table2;

Re: interval match

sunny_talwar — Mon, 20 Nov 2017 12:51:44 GMT

Not sure how you are getting the Price? Can you elaborate? I used this script currently

Table1:

LOAD * INLINE [

Product, Quantity_Sold

A, 5

A, 10

A, 5

A, 12

A, 8

B, 5

B, 10

];

Table2:

LOAD * INLINE [

Product, Start, End, Price

A, 1, 3, 100

A, 4, 15, 110

A, 16, 30, 120

A, 31, 40, 150

B, 1, 3, 160

];

Left join(Table1)

Intervalmatch(Quantity_Sold, Product)

LOAD Start,

End,

Product

RESIDENT Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

Re: interval match

Anonymous — Mon, 20 Nov 2017 12:54:51 GMT

I want to check if the Quantitiy_Sold lies with in start end range, and multiply End*Price.

If it exceeds the range it should check next start end range.

For eg, first row (3*100)+(2*110)=520

Re: interval match

Anonymous — Mon, 20 Nov 2017 16:38:45 GMT

Any particular solution or function that i need to use to get the desired result??