interval match

[Anonymous]

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.

[sunny_talwar]

May be you need extended IntervalMatch here

Left join(Table1)

Intervalmatch(Quantity_Sold, Product)

LOAD Start,

    End,

    Product

RESIDENT Table2;

drop table Table2;

[sunny_talwar]

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;

[Anonymous]

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

[Anonymous]

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