topic Loading data grouping for particular value of a dimension in QlikView

Loading data grouping for particular value of a dimension

Sat, 09 Apr 2011 06:47:24 GMT

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

Loading data grouping for particular value of a dimension

stevedark — Sun, 10 Apr 2011 08:21:12 GMT

Hi there,

It sounds to me that what you need is simply a Pivot table based on your month.

To do this go from your initial data set and add a new Chart. Select to display this in a Pivot view. Set the dimensions to be OrderDescription, OrderYear and OrderMonth. Add a single expression of MonthlyOrderedAmount. On the properties page check the values for Allow Pivotting and Always Fully Expanded.

Once your chart has been rendered you can drag the year to the top of the page (click and hold the label to do this). Then do the same for the Month field to drag it under the Year field.

If you like you can put totals in by selecting the Properties tab and clicking the Show Partial Sums box for both Year and Month dimensions.

You will probably want to have better formatted date dimensions, and this should be done in your initial load before writing to QVD, with something like this in your load script:

OrderYear as [Order Year],
Month(MakeDate(OrderYear, OrderMonth, 1)) as [Order Month],
Date(MakeDate(OrderYear, OrderMonth, 1), 'MMM-YY') as [Order Month Year],

Formatting the dimensions like this will make it easier to get the legends you want when you build more charts. Having a combined month year can be particularly useful.

Hope that helps,

Steve

Loading data grouping for particular value of a dimension

Sun, 10 Apr 2011 21:22:37 GMT

Hi Steve, thanks for your reply. Also I think to use a pivot table, but I have some doubts.

I need to show also OrderID, so the dimensions should be 1) OrderID, 2) OrderDescription, 3) OrderYear and 4) OrderMonth.

I need to show also the TotalOrderAmount: it isn't the sum of the monthly amount but it's an expected amount. Moreover I could need to show the monthly invoiced amount.

Now, in this case could a table box be better than a pivot chart?

Thanks

Loading data grouping for particular value of a dimension

stevedark — Sun, 10 Apr 2011 21:59:58 GMT

Hi there,

You can simply add the OrderID as one of the dimensions of the pivot, this will not cause any issues. You can also have two expressions in your pivot - the sum of the monthly amount and the sum of the total amount. I would imagine however that if you are including the TotalOrderAmount you will need to be very careful what partial sums you include - as you would not want to double count totals across months.

Regards,
Steve

Loading data grouping for particular value of a dimension

Mon, 11 Apr 2011 07:26:10 GMT

Hi.

You say me that with pivot chart I'm free to add other descriptive attributes that I must manage as a dimension, isn'it?

But if I use a pivot chart it couldn't be necessary to use a LOAD with JOIN to calculate the monthly order amounts (see my posted code), isn't it?

The TotalOrderAmount is an expected amount, but the MonthlyOrderAmount is an actual amount.

Thanks

Loading data grouping for particular value of a dimension

Mon, 11 Apr 2011 08:14:55 GMT

Hi,

I have tried to use the pivot chart with the expanded dimensions. In the char the drag & drop is allowed but I cannot to move the Year dimension on the top. I want to show the monthy order amount on the columns:

1st col ... 2nd column ............. 3rd column ................ 4th col ... 5th column ..................... 6th column ....................... 7th column ...............................

OrderID ... OrderDescription ... TotalOrderAmount ... Year ....... JanuaryOrderAmount ... FebruaryOrderAmount ... MarchOrderAmount and so on

The representation should be like an Excel spreadsheet.

Thanks

Loading data grouping for particular value of a dimension

stevedark — Mon, 11 Apr 2011 11:33:17 GMT

Hi there,

From your further explanation it sounds like TotalOrderAmount should be another dimension, rather than an expression. It also sounds that Year does not need to be dragged about, but month does. You need to ensure that Allow Pivoting is selected on the Properties tab. Dragging dimensions can sometimes be fiddly getting things to anchor in the right place. As you drag the month you need to wait for a horizontal blue line to appear before letting go of your left mouse button.

If you upload an example of where you are at presently and an Excel representation of where you are trying to get to I can take a look at this for you.

Regards,
Steve

Loading data grouping for particular value of a dimension

Mon, 11 Apr 2011 12:10:05 GMT

Hi,

yes TotalOrderAmount for me is a dimension in the chart.

I'me able to place year and month dimensions in the right positions but after eight dimensions when I add a dimension it goes below year and month and I cannot move it correctly.

I attach an img. See Importo contratto below Anno (or Year in English) and Mese (or Month in English). Thanks

P.S.: no data are showed for the selections.

Loading data grouping for particular value of a dimension

stevedark — Mon, 11 Apr 2011 15:10:09 GMT

Hi,

You should still be able to drag dimensions even with nine dimensions. Sometimes though it is neccesary to change the order of dimensions by using the Promote and Demote buttons on the Expressions tab of the chart's properties to get things in the right order.

Regards,
Steve

Loading data grouping for particular value of a dimension

Mon, 11 Apr 2011 15:31:12 GMT

Hi,

I'm not able to move Importo Contratto dimension in the right position also even playing with the order in the Dimensions tab.

Thanks

Loading data grouping for particular value of a dimension

stevedark — Tue, 12 Apr 2011 22:21:57 GMT

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

Loading data grouping for particular value of a dimension

Thu, 14 Apr 2011 12:21:27 GMT

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

Loading data grouping for particular value of a dimension

stevedark — Thu, 14 Apr 2011 20:17:58 GMT

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