FirstSortedValue of text

[Anonymous]

I have two loaded tables that look like this:

Order ID    Item ID

     1               12

     1               18

     2               9

Item ID       Item Description

     12               Soap

     18               Bread

     9                 Nuts

and I would like to produce a straight table with this data

Order ID         Order Description

     1                    Soap

     2                    Nuts

I tried using FirstSortedValue(Item Description, Order ID) but it returns null for Orders that have more than one item. Is this how I should be trying to do it?

[swuehl · Accepted solution]

What about

=FirstSortedValue([Item Description], [Item ID])

[Anonymous]

That worked, thank you.

[alexandros17]

This is the code you need, let me know

AAA:
LOAD * Inline [
Order ID, Item ID
1 , 12
1 , 18
2 , 9
];

Left Join

LOAD * Inline [
Item ID , Item Description
12 , Soap
18 , Bread
9 , Nuts
];

Inner Join
LOAD [Order ID],  FirstValue([Item ID]) as [Item ID] /*, [Item Description]*/ Resident AAA Group By [Order ID];

[MK_QSL]

Create a Straight Table

Dimension

1)     =Aggr(Min([Item ID]),[Order ID])

2) Order ID

Expression

FirstSortedValue([Item Description], Aggr(Min([Item ID]),[Order ID]))

Presentation Tab

Select [Order ID] and Tick Hide Column