First value within a dimension of a pivot table

richard_chilvers — Thu, 23 Oct 2014 10:43:19 GMT

I have the following table (example)

PRODUCT BOOKING_REF SURNAME

Prod 1 Ref100 Smith

Prod 1 Ref100 Simpson

Prod 1 Ref101 Jones

Prod 1 Ref101 James

Prod 2 Ref102 Simons

etc.....

I need a pivot table where PRODUCT and BOOKING_REF are dimensions, and showing just the first SURNAME for each BOOKING_REFon each row. So:

Prod 1 Ref100 Smith

Prod 1 Ref101 Jones

Prod 2 Ref102 Simons

I kind of expected the FIRST function to work as my expression, but I just get NULL values. Struggling to understand the documented definition of this function. Any help or solutions ?

Thanks

Re: First value within a dimension of a pivot table

JonnyPoole — Thu, 23 Oct 2014 14:12:59 GMT

I added a key to your load called recordnumber. This allows me to number each line of data 1,2,3...

Then i used firstsortedvalue() to pull the surname with the lowest recordnumber for each combination of the other 2 dimensions.

Attached is full sample

topic First value within a dimension of a pivot table in QlikView

First value within a dimension of a pivot table

Re: First value within a dimension of a pivot table