topic Re: Dollar Sign Expansion of Aggr Expression in QlikView

Dollar Sign Expansion of Aggr Expression

effinty2112 — Wed, 09 Dec 2015 11:40:01 GMT

Hi All,

I have the following expression:

concat(Aggr(if(Aggr(Sum(Qty), POItemID)>= 250,POItemID), POItemID), ', ',POItemID)

which returns this string when placed in a textbox

17, 18, 28, 59, 61, 63, 64, 93, 96, 97, 190, 191, 192, 205, 224, 225, 266, 267, 279, 280, 324, 325, 331, 332, 477, 478, 479, 501, 554, 555, 561, 562, 563, 565, 567, 569, 570, 664, 665, 703, 726, 727, 741, 763, 764, 821, 933, 934, 935, 950, 951, 952, 1005, 1011, 1032, 1033, 1084, 1085, 1086, 1119, 1120, 1184, 1188, 1189, 1196, 1226, 1269

My problem is that I want to use this expression in a set modifier. When I wrap it in a dollar sign expansion

=$(=concat(Aggr(if(Aggr(Sum(Qty), POItemID)>= 250,POItemID), POItemID), ', ',POItemID))

I get the result

Error: Garbage after expression: ","

I've replaced the comma with chr(44) with the same result.

I've replaced the comma with a '|' hoping that later I can wrap with a Replace function to swap the '|' for ',' but I get this

Error: Garbage after expression: "|18|28|59|61|63|64|93|96|97|190|191|192|205|224|225|266|267|279|280|324|325|331|332|477|478|479|501|554|555|561|562|563|565|567|569|570|664|665|703|726|727|741|763|764|821|933|934|935|950|951|952|1005|1011|1032|1033|1084|1085|1086|1119|1120|1184|1188|1189|1196|1226|1269"

Which is especially odd because before DSE the expression returned this:

17|18|28|59|61|63|64|93|96|97|190|191|192|205|224|225|266|267|279|280|324|325|331|332|477|478|479|501|554|555|561|562|563|565|567|569|570|664|665|703|726|727|741|763|764|821|933|934|935|950|951|952|1005|1011|1032|1033|1084|1085|1086|1119|1120|1184|1188|1189|1196|1226|1269

The DSE dropped the first element.

Advice would be very welcome.

Kind regards

Andrew

Re: Dollar Sign Expansion of Aggr Expression

sunny_talwar — Wed, 09 Dec 2015 11:45:25 GMT

Would you be able to share a sample?

Re: Dollar Sign Expansion of Aggr Expression

sunny_talwar — Wed, 09 Dec 2015 11:59:25 GMT

This seems to be working for me:

{<POItemID = {$(=Concat(Aggr(if(Aggr(Sum(Qty), POItemID)>= 250,POItemID), POItemID), ', ',POItemID))}>}

Re: Dollar Sign Expansion of Aggr Expression

marcus_sommer — Wed, 09 Dec 2015 12:04:11 GMT

Try it with single-quotes around the $-sign expansion then the content is a string:

='$(=concat(Aggr(if(Aggr(Sum(Qty), POItemID)>= 250,POItemID), POItemID), ', ',POItemID))'

- Marcus

Re: Dollar Sign Expansion of Aggr Expression

Anonymous — Wed, 09 Dec 2015 12:10:59 GMT

You need to put your $ expression into single quotes like ' ',something like suggested by Marcus

Re: Dollar Sign Expansion of Aggr Expression

effinty2112 — Wed, 09 Dec 2015 12:26:53 GMT

Thanks Sunny, Marcus spotted that I had missed single quotes - I can be a bit stupid that way. Thanks again for your time.

Re: Dollar Sign Expansion of Aggr Expression

effinty2112 — Wed, 09 Dec 2015 12:27:52 GMT

Thanks Marcus!