topic Re: Help to understand Array syntax in QlikView

Help to understand Array syntax

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

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!

Re: Help to understand Array syntax

Anil_Babu_Samineni — Mon, 26 Jun 2017 06:13:30 GMT

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

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 06:15:38 GMT

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.

Re: Help to understand Array syntax

Anil_Babu_Samineni — Mon, 26 Jun 2017 06:19:31 GMT

Where you are doing this? Script / Front end?

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 06:21:04 GMT

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

Re: Help to understand Array syntax

Anil_Babu_Samineni — Mon, 26 Jun 2017 06:40:02 GMT

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?

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 06:45:55 GMT

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.

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 07:23:39 GMT

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

ArrayTable:

LOAD

Contract__Number,

Min_Quantity,

Price

;

SQL SELECT

Contract__Number,

Min_Quantity,

Price

FROM "****

;

And as the result

Re: Help to understand Array syntax

Kushal_Chawda — Mon, 26 Jun 2017 07:55:05 GMT

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

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 08:14:19 GMT

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 $.

Re: Help to understand Array syntax

Kushal_Chawda — Mon, 26 Jun 2017 09:03:09 GMT

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

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 09:06:37 GMT

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

Re: Help to understand Array syntax

swuehl — Mon, 26 Jun 2017 09:14:07 GMT

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;

Re: Help to understand Array syntax

sculptorlv — Mon, 26 Jun 2017 09:24:13 GMT

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?

Re: Help to understand Array syntax

swuehl — Mon, 26 Jun 2017 09:31:04 GMT

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