Loading data grouping for particular value of a dimension

[#1]

Hi,

I have a Orders table, stored in a QVD and to load, like this:

OrderID, OrderDescription, TotalOrderAmount, OrderYear, OrderMonth, MonthlyOrderedAmount.

An order can have one or more monthly lines for year. In the table, the primary key is composed of OrderID, OrderYear and OrderMonth.

I want to load the orders to have the monthly OrderedAmount on columns to show them in a tabular format. It is important to show the OrderID, the OrderDescription and the TotalOrderAmount. This last amount is repeated for each monthly order row.

I think to use this code:

(pre-load into resident table Orders)

OrdersByMonth:

LOAD DISTINCT OrderID, OrderYear, OrderMonth, OrderDescription, TotalOrderAmount RESIDENT Orders;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as JanuaryOrderedAmount RESIDENT Order WHERE OrderMonth = 1 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as FebruaryOrderedAmount RESIDENT Order WHERE OrderMonth = 2 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP

LOAD OrderID, OrderYear, OrderMonth, SUM(MonthlyOrderedAmount) as MarchOrderedAmount RESIDENT Order WHERE OrderMonth = 3 GROUP BY OrderID, OrderYear, OrderMonth;

INNER KEEP ... and so on ...

Is this a good solution or is there a better one? Any helps to me, please?

Many thanks

[stevedark]

Hi there,

If you are wanting this value to appear along side the other expressions then you need to make it an expression also (rather than a dimension). It is always safest to put an aggregation around values in expressions. If SUM gives you the wrong answer (due to double counting) then you could try a max (ie. max(ImpContratto) ).

Another observation, looking at your data model you have done a number of resident loads to get unique lists of your key fields in separate tables. You do not need to do this - QlikView will effectively create unique lists in memory anyway. Those resident loads will only slow your load script and cause performance issues in the front end.

Good luck with getting it all how you want.

Regards,
Steve

[#3]

Hi.

ImpContratto is unique for CodiceCommessa value, but the key of original table is composed of CodiceCommessa, Anno (or Year) and Mese (or Month). So, ImpContratto should be duplicated. Why does it move this amount in the expressions? Is it not possible to manage it as a dimension? The issue is a little different. If I should have another dimension instead of ImpContratto the problem remains.

The resident loads that you say are made to create the dimensions. The keys is CodiceCommessa + Anno + Mese and so there aren't distinct values for Anno or Mese in the main load.

Thanks

[stevedark]

Hi there,

QlikView keeps expressions and dimensions separate in Pivot tables. If you want that value to appear with the expressions you need to also make it an expression - and resolve the duplicate value issues with a max statement. If you have another text dimension then it won't belong with the expression values and should sit fine with the other dimensions.

You do not need to have tables with distinct values in to use them as dimensions. QlikView automatically shows only distinct values. If you remove the resident loads where you are simply loading a single field with a group by statement you should see no change in functionality.

Regards,

Steve