Get any one of the string?

[user467341]

Hi,

I have this straight table:

ref	item_no	item_name	category	cost
234	783JH1	Audi A1	Vehicle	$100,000
234	YA21H	Audi A1	Vehicle	$100,000
234	UHA13	Audi A1	Vehicle	$100,000
582	AHDQ9	Honda Fit	Vehicle	$25,000
911	H891JA	Mazda CX5	Vehicle	$80,000

There are rows where ref, item_name, category and cost are the same but not the item_no (eg row 1 to 3)

 I want the table to output as this:

ref	item_no	item_name	category	cost
234	783JH1	Audi A1	Vehicle	$100,000
582	AHDQ9	Honda Fit	Vehicle	$25,000
911	H891JA	Mazda CX5	Vehicle	$80,000

I want the straight table to only take ANY one of the item_no (either 783JH1 or YA21H or UHA13) and just show one row in the straight table.

HOw can I achieve this in straight table? Thanks

[BrunPierre]

Hi, apply this for the cost expression while using the rest of the fields as dimensions.

 =FirstSortedValue(cost,Aggr(Sum(cost), ref))

[user467341]

Hi. My cost column is actually a dimension. Is there a way to apply on the item_no column only?

[BrunPierre]

Data:
LOAD ref,
item_no,
item_name,
category,
cost
FROM Source;

Inner Join
LOAD ref,
FirstValue(item_no) as item_no

Resident Tmp
Group By ref;

[user467341]

Thanks. Is there no way to do this with set expression?

[BrunPierre]

item_no as a calculated dimension.

  =Aggr(MinString(item_no), cost)

[user467341]

Hi. I think we are almost there but noticed that some data return NULL because NULL is the shortest string. How can I modify this 

  =Aggr(MinString(item_no), cost)

to exclude NULL?

[user467341]

  =Aggr(MinString(item_no), cost)

[BrunPierre]

Try.

=Aggr(MinString({<item_no={'*'}>} item_no), cost)

[Sebastian_Dec]

I know that what I write is not a solution, but if you want to do it along the lowest resistance line, remove from the table: item_no and item_name, you wll have this:

ref	category	cost
234	Vehicle	$100,000
582	Vehicle	$25,000
911	Vehicle	$80,000

[user467341]

Hi, this doesn't work. It still returns NULL instead of ignoring it.