topic How to group specific values in a table? in QlikView

How to group specific values in a table?

Tue, 15 Nov 2011 22:00:43 GMT

Hi Everybody!

I'm trying to group one table (Prices) with one specific value to be calculated over a volume table.

I have in the table Prices in one month 3 levels (Product, Destination, Type), but I only want Product and Destination and in this table I need an average of the results.

I have tested it via Group by, but the results is not satisfactory yet, calculating the table volumes over prices, the values are being duplicated.

Please see below some details about it:

I have the following script behind:

//////////////

test:

LOAD Month,

Product,

Destination,

Type,

Month&Product&Destination as Link,

price

FROM

teste.xlsx

(ooxml, embedded labels, table is Price);

test3:

left join

Load

Link as Link,

avg(price) as average

Resident test

group by Link;

Testex:

LOAD Link as Link,

Quantity

FROM

C:\Users\chduartel\Desktop\Group\teste.xlsx

(ooxml, embedded labels, table is Quantity);

/////////////////////////

Could anybody help me to create a simple solution for it? (field average one time vs. sum of quantity)

Please note, the solution of doing the formula avg(average) + sum(Quantity) doesn't work without detailing the months, products and destination.

Please find enclosed the files containing the information.

Thanks in advance,

Leandro Duarte

Re: How to group specific values in a table?

swuehl — Tue, 15 Nov 2011 23:41:31 GMT

Maybe like this? I calculated also the sum of quantities in the script (do you need it to be selection sensitive?)

Script looks like this:

test:

LOAD Month,

Product,

Destination,

Type,

Month&Product&Destination as Link,

price

FROM

teste.xlsx

(ooxml, embedded labels, table is Price);

Testex:

LOAD Link as Link,

Quantity

FROM

teste.xlsx

(ooxml, embedded labels, table is Quantity);

test2:

Load Link, sum(Quantity) as SumQuantity resident Testex group by Link;

test3:

left join

Load

Link as Link,

avg(price) as average

Resident test

group by Link;

Hope this helps,

Stefan

How to group specific values in a table?

Wed, 16 Nov 2011 19:13:28 GMT

Hi, I have tested with Load Distinct... any other suggestion?

Re: How to group specific values in a table?

swuehl — Thu, 17 Nov 2011 13:57:32 GMT

Sorry,

I did not get your point. You tested with Load Distinct?

Since I have not used a load distinct (as far as I remember and can see from my post), what do you want to tell me?

If my above code does not work for your problem, please specify, what the exact problem is.

Regards,

Stefan

How to group specific values in a table?

Thu, 17 Nov 2011 14:35:20 GMT

but why dont you use sum(Quantity) * avg(price) as expresion?

How to group specific values in a table?

Thu, 17 Nov 2011 14:50:31 GMT

or if you want to calculate average price in script:

//////////////

test:

LOAD Month,

Product,

Destination,

Type,

Month&Product&Destination as Link,

price

FROM

teste.xlsx

(ooxml, embedded labels, table is Price);

Testex:

LOAD Link as Link,

Quantity

FROM

C:\Users\chduartel\Desktop\Group\teste.xlsx

(ooxml, embedded labels, table is Quantity);

left join

Load

Link as Link,

avg(price) as average

Resident test

group by Link;

/////////////////////////