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]

Hi 

try with 

=Aggr(If(Match(Left(ItemNumber,6),'PPT170'),'PPT Item',CustomDescription),OrderNumber,ItemNumber)

[asinha1991]

so if I understand you correct, this new dimension will be at order level right?

try this

=if(Aggr(count(If(wildmatch(ItemNumber,'PPT170*'),CustomDescription)),Order_Number)>0,'PPT Item',CustomDescription)

[qlikviewaf]

With this formula, only the item PPT17014 is set as "PPT Item"; i would like to have all items belonging to the same order number having PPT Item.

I explained my self?
Thank you

[qlikviewaf]

With this formula, only the item PPT17014 is set as "PPT Item"; i would like to have all items belonging to the same order number having PPT Item.

I explained my self?
Thank you

[asinha1991]

did you try what I suggested?

this should work at order level

[qlikviewaf]

yes, i tried, exactly same results.. 

[StarinieriG]

Could you create a new dimension inside script? 

[qlikviewaf]

yes i can

[asinha1991]

maybe this

=if(Aggr(alt(count(Total If(wildmatch(ItemNumber,'PPT170*'),CustomDescription)),0),Order_Number)>0,'PPT Item',CustomDescription)