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!

[Anil_Babu_Samineni]

Are you sure, The first client should come up Price is 4. I am not getting the logic behind?

[sculptorlv]

The first client bought 15 (from table 1), than is more than 10 with price 4, but less than 16 with price 2 (table 2). So, client needs to pay price 4.

[Anil_Babu_Samineni]

Where you are doing this? Script / Front end?

[sculptorlv]

First, I need to Load data from Tables 1 and 2 in SQL and Qlick LOAD part (script??).

Then I will use this info in order to generate Table 2 - report (front end??)

[Anil_Babu_Samineni]

First part is we can fetch easily

Second part may be you need like below, Because From table 2 you are getting Price where Max value of MinQuantity?

[sculptorlv]

Sorry, I didn't get the idea of your answer.

Anyway, I will try to rephrase my needs.

I have the following situation:

The clients paying prices are depended on quantity.

For example, client 1 pays 10$ for inventory, if he buys less than 10 kg.

Then, if clients will buy 10 kg or more (but quantity will no exceed 16 kg) he will have to pay lower rent price - 4$.

Finally, if client will buy 16 kg or more - the rent price will be only 2$.

My general task is to make an analysis, how much each client must pay rent price.

[sculptorlv]

Can somebody helps with syntax at the beginning? What I do wrong?

Let aListLength = NoOfRows(ArrayTable);

FOR zi = 1 to aListLength -1

Let aList$(zi) = Peek(Contract__Number, zi, ArrayTable);

     Next

ArrayTable:

LOAD

Contract__Number,

Min_Quantity,

Price

;

SQL SELECT

Contract__Number,

Min_Quantity,

Price

FROM "****

;

And as the result

[Kushal_Chawda]

For client 1, Minquantity is zero, so Proce should be 10, isn't it?

[sculptorlv]

Nope

I check, how much client bought and give him corresponding price.

It client 1 buys less than 10 kg, his rent price will be 10 $.

But client 1 bought 14 kg,  then hi got new price level 4 $.

In case of quantity of 16 kg and more for client 1, the price will be 2 $.