topic Re: Grouping expression question (with demo application) in QlikView

Grouping expression question (with demo application)

alexis — Thu, 04 Oct 2012 12:46:31 GMT

I attach a demo application (see attached qvw and xls).

In this demo application I have 3 tables:

a) Clients (linked to Orders via ClientID)

b) Orders (linked to Prices via OrderID)

c) Prices

For every order there are multiple records in Prices. Please also note the field "Type" that is present in both "Orders" and "Prices" (but are not linked).

As it explains in the yellow box, I want to extend the Orders table with 2 additional columns "MaxPrice" and "MinPrice" that will contain the maximum and minimum prices for that OrderID/Type combination.

This will be achieved (I suspect) by traversing the Prices table for every Order and the result should be as in the BLUE table (see screen shot).

Please help as I am stuck - simply doing Max(Price) and Min(Price) does not cover for the "Type" part as explained above.

Thanx in advance

Alexis

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 00:08:21 GMT

Anyone - I am quite desperate!

Re: Grouping expression question (with demo application)

MayilVahanan — Fri, 05 Oct 2012 00:44:20 GMT

Price is in string format.. So only its can't calculate it.. Please change format..Use Num#() in script or change in the xls ile itself to convert into number format..

Hope it helps

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 00:59:00 GMT

Thanks for pointing that out

I now have a spreadsheet with values in the correct format and a new version of the application using this data.

The problem has not been resolved but at least now we are dealing with real numeric data.

Any suggestions - I would really appreciate them..

regards

Alexis

Re: Grouping expression question (with demo application)

Fri, 05 Oct 2012 03:42:31 GMT

Hi,

Maybe you can try this one. I have made several adjustments like renaming OrderType as Type, suppressing null values and aggregation.

Regards,

Janzen

Re: Grouping expression question (with demo application)

Fri, 05 Oct 2012 08:33:35 GMT

Hi Alexis !

Why don't you simply use

=max(if(Type = OrderType, Price))

and

min(if(Type = OrderType, Price))

in a straight table?

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 09:37:04 GMT

Hi Nicolas

That was very helpful indeed.

For reasons that are too complex to explain here, I wish to record these valiues on the Orders table rather than calculating them at display time - is that possible?

Best regards

Alexis

Re: Grouping expression question (with demo application)

Fri, 05 Oct 2012 09:48:16 GMT

Alexis,

If you want to use a "Table" object instead of a "Chart" object, you can't use expressions, you are forced to use existing dimensions.

So, I suggest you to add 2 fields in your table in the script:

LEFT JOIN (Orders)
LOAD
          OrderID,
          OrderType AS Type,
          max(Price) AS Max_Price,
          min(Price) AS Min_Price
RESIDENT Prices
GROUP BY OrderID, OrderType;

So, for each OrderID / OrderType, you'll have a Min and a Max price.

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 10:09:33 GMT

Thanx Nicolas - that is spot on.

Because the "Prices" table will be potentially very large in size is there a way we can do something similar to the Prices table - in other words end up with a reduced Prices table that will contain:

MinMaxPrices:

- OrderID,

- OrderType,

(- PriceDate,)

(- PriceTime,)

(- Price)

- MaxPrice

- MinPrice

obviously the bracketed fields will not feature in this summary table.

Re: Grouping expression question (with demo application)

Fri, 05 Oct 2012 10:24:53 GMT

In my exemple, the data are stored in the "Orders" table, so even if the Prices table is very huge, there will be only 1 value for each couple "OrderID / Type".

Because the query has a resident table as source, this is very fast.

(I deal with tables with billions of lines)

If you want to create a separate table, use the same technique:

MinMaxPrices:
LOAD
          OrderID AS MinMaxPrices.OrderID,
          OrderType AS MinMaxPrices.OrderType,
          max(Price) AS Max_Price,
          min(Price) AS Min_Price
RESIDENT Prices
GROUP BY OrderID, OrderType;

You'll have only 1 ligne for each couple "OrderID / OrderType".

But this table won't be in relation with the "Orders" table, so if you want to display ClientID and OrderPrice, you'll need to add a JOIN.

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 10:29:49 GMT

This application is using BILLIONS of price rows!!!

The demo application that I created is not the appliction that I created - it is merely a way of demonstrating the requirement.

Thank you very much for your help..

I selected your previous answer as the Correct answer in the end!!

Alexis

Re: Grouping expression question (with demo application)

Fri, 05 Oct 2012 10:34:54 GMT

> The demo application that I created is not the appliction that I created - it is merely a way of demonstrating the requirement.

Of course !

Just to be curious, could you tell me how many time it takes:

- to load the "prices" table the first time

- to load the "orders" table

- to make the LEFT JOIN / GROUP BY, using the resident table.

I bet the join is very faster than the other loads.

Re: Grouping expression question (with demo application)

alexis — Fri, 05 Oct 2012 10:58:16 GMT

I will let you know as soon as I have made some more progress for sure.

Thanx again

Alexis