Aggregate Function

[qlikviewaf]

Hi All,

i have the following data structure.

I would like to create new dimension that works in the following way:

If at "order number" level there's one item number that starts with PPT170* then the new dimesion needs to be 'PPT Item' otherwise use "CustomDescription".

i used this formula but is not working:

=Aggr(If(Match(ItemNumber,'PPT170*'),'PPT Item',CustomDescription),Order_Number)

any idea?

[StarinieriG]

Then maybe this could work:

TAB:

LOAD * From YourTable;

Left Join (TAB)
LOAD
OrderNumber,
Concat(distinct If(Left(ItemNumber,6)='PPT170',ItemNumber),',') AS NewField_temp
Resident TAB
Group By
OrderNumber
;

NoConcatenate
NEW:
LOAD *,
If(Len(NewField_temp)>0,'PPT Item',CustomDescription) as NewField
Resident TAB;

DROP Table TAB;