Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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