topic Re: Aggregate Function in QlikView

Aggregate Function

qlikviewaf — Wed, 09 Oct 2019 08:48:18 GMT

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?

Re: Aggregate Function

StarinieriG — Wed, 09 Oct 2019 08:59:37 GMT

try with

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

Re: Aggregate Function

asinha1991 — Wed, 09 Oct 2019 09:01:35 GMT

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)

Re: Aggregate Function

qlikviewaf — Wed, 09 Oct 2019 09:28:44 GMT

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

Re: Aggregate Function

qlikviewaf — Wed, 09 Oct 2019 09:36:50 GMT

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

Re: Aggregate Function

asinha1991 — Wed, 09 Oct 2019 10:06:05 GMT

did you try what I suggested?

this should work at order level

Re: Aggregate Function

qlikviewaf — Wed, 09 Oct 2019 10:13:50 GMT

yes, i tried, exactly same results..

Re: Aggregate Function

StarinieriG — Wed, 09 Oct 2019 12:04:32 GMT

Could you create a new dimension inside script?

Re: Aggregate Function

qlikviewaf — Wed, 09 Oct 2019 12:38:59 GMT

yes i can

Re: Aggregate Function

asinha1991 — Wed, 09 Oct 2019 12:42:07 GMT

maybe this

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

Re: Aggregate Function

StarinieriG — Wed, 09 Oct 2019 13:40:57 GMT

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;