topic Re: FirstSortedValue of text in QlikView

FirstSortedValue of text

Anonymous — Fri, 03 Jul 2015 13:54:47 GMT

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?

Re: FirstSortedValue of text

swuehl — Fri, 03 Jul 2015 14:01:54 GMT

What about

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

Re: FirstSortedValue of text

Anonymous — Fri, 03 Jul 2015 14:04:38 GMT

That worked, thank you.

Re: FirstSortedValue of text

alexandros17 — Fri, 03 Jul 2015 14:04:43 GMT

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];

Re: FirstSortedValue of text

MK_QSL — Fri, 03 Jul 2015 14:05:13 GMT

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