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

[MayilVahanan]

Hi

Try like below

=Aggr(MinString({<item_no={"=Len(Tirm(item_no))>0"}>} item_no), cost)

[user467341]

Hi. Can't work... NULL is still present.

[MayilVahanan]

Hi

Just to understand, you have null value and some other values [item no] available for one ref? 

may be, try with Maxstring and also, can you post the sample data for this scenario?

=Aggr(MaxString({<item_no={"=Len(Tirm(item_no))>0"}>} item_no), ref)

[BrunPierre · Accepted solution]

You actually have 'NULL' as a value right? then try

=Aggr(MinString({$-<item_no={"NULL"}>} item_no), cost)