Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to group specific values in a table?

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:

Untitled.png

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to group specific values in a table?

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

5 Replies
MVP
MVP

Re: How to group specific values in a table?

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

Not applicable

How to group specific values in a table?

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

MVP
MVP

Re: How to group specific values in a table?

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

Not applicable

How to group specific values in a table?

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

Not applicable

How to group specific values in a table?

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;

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