topic Re: max(description) in Dimension in QlikView

max(description) in Dimension

Mon, 03 Sep 2012 13:17:15 GMT

I have loaded a product detail reference table which contains productcode, productdesc and productmastercode as well as a field I made in the load from productdisplay which is the productcode & '-' & productdesc. There can be a one to many relationship between productmastercode and productcode.

I have built a pivot table in which I have as dimensions product hierarchy then productmastercode then productdisplay and this works fine. However what I would like is that the dimension for productmastercode be a calculated one that is the productmastercode & ' - ' & 'one of the productdesc'

similar to the way in TSQL you can do max(productdesc). I tried max() and maxstring() but both gave a Error in calculation error in the pivot table

Any and all help much appreciated.

Mark

Re: max(description) in Dimension

Mon, 03 Sep 2012 13:19:12 GMT

you should use a function aggr() or firstsortvalue

Re: max(description) in Dimension

Mon, 03 Sep 2012 13:29:44 GMT

HI Marco

I tried ....

productmastercode & ' - ' & firstsortedvalue(productdesc, productmastercode)

and this gave the Error in calculation

I also tried ......

aggr(nodistinct productdesc, productmastercode)

this gave results on all lines except the ones where there was a one to many relationship (also tried distinct)

Re: max(description) in Dimension

whiteline — Mon, 03 Sep 2012 13:30:23 GMT

Use as calculated dimension:

=aggr(productmastercode & ' - ' & productdesc, productmastercode, productdesc)

Re: max(description) in Dimension

Mon, 03 Sep 2012 13:39:53 GMT

That is placing a desc beside each line but it is not maintaining the master code piece....

Master Code Product Code Product Desc aggr()

111111 222222 Test Line 1 111111 - Test Line 1

333333 444444 Test Line 2 333333 - Test Line 2

333333 555555 Test Line 3 333333 - Test Line 3

I would like the aggr dimension to in the above display 333333 - Test Line 2 for both lines so that then in the pivot table setup it would allow for a group view of the two product that i can then plus out to. the aggr as suggested by whiteline shows the mastercode with a description but a new line for each desc so no chance to group on the mastercode.

hope that makes sense

Re: max(description) in Dimension

whiteline — Mon, 03 Sep 2012 13:50:34 GMT

=aggr(productmastercode & ' - ' & firstsortedvalue(productdesc, fieldindex('productdesc', productdesc), productmastercode)

fieldindex('productdesc', productdesc) - gives the load order sorting for productdesc.