Grouping expression question (with demo application)

[alexis]

Hi

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

[alexis]

Anyone - I am quite desperate!

[MayilVahanan]

Hi

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

[alexis]

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

[#5]

Hi,

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

Regards,

Janzen

[#6]

Hi Alexis !

Why don't you simply use

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

and

min(if(Type = OrderType, Price))

in a straight table?

[alexis]

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

[#8 · Accepted solution]

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.

[alexis]

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.

[#10]

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.