Help to understand Array syntax

[sculptorlv]

Hello guys, I hope for your help. I tried to read manuals, but didn't find answer about the use of  arrays.

My task is..

I have two tables in data base.

Table 1. (sales)

Client nr.  Quantity

1               15

2               3

3               4

Table 2. (limits)

Client      MinQuantity     Price

1               0                         10

1               10                         4

1               16                         2

2               0                         10

2               3                           5

3               0                         10

I need to load these data somehow in order to made the following analysis:

    Compare how much clients bought quantity (from table 1) with maximal MinQuantity (in table 2)  and get corresponding price.

The result must be:

Client Quantity Price

1          15               4

2          3                  5

3          4                  10

I hope, You can get the idea and help me. Thank you in advance!

[Kushal_Chawda]

In the given below example, how you are getting Price 4 for client 1?

Client      MinQuantity     Price

1               0                         10

1               10                         4

1               16                         2

2               0                         10

2               3                           5

3               0                         10

[sculptorlv]

The table 2 says:

if client 1 buys from 0 kg to 10 (less than 10) - he will get price 10

if client 1 buys from 10 to 16 kg (less than 16) - he will get price 4

if client 1 buys form 16 to unlimited - he will get price 2

[swuehl · Accepted solution]

You can use IntervalMatch():

Sales:

LOAD * INLINE [

Client,  Quantity

1        ,       15

2        ,       3

3        ,       4

];

Limits:

LOAD * INLINE [

Client,      MinQuantity,     Price

1,               0      ,                   10

1,               10     ,                    4

1,               16     ,                    2

2,               0      ,                   10

2,               3      ,                     5

3,               0      ,                   10

];

Limits2:

LOAD Client, MinQuantity as QtyFrom, If(Peek(Client) <> Client, 1E6, Previous(MinQuantity)-1 ) as QtyTo, Price

RESIDENT Limits

Order by Client, MinQuantity desc;

DROP TABLE Limits;

JOIN (Sales)

IntervalMatch( Quantity, Client)

LOAD QtyFrom, QtyTo, Client

Resident Limits2;

LEFT JOIN (Sales)

LOAD * Resident Limits2;

DROP TABLE Limits2;

[sculptorlv]

Thank you, this works.

But can you please help me to create these tables form data base?

For example I have simple SQL select (for table 2):

SQL SELECT

     Contract_Number, //Client nr.

     Min_Quantity,

     Price

FROM DataBase ;

How can I make an array from this - each client have a lot of min_quantity and prices?

[swuehl]

Just use your database table sources instead of the INLINE table sources, if I understand your question correctly.