topic Re: remove first brackets on concatenated expression in QlikView

remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 08:36:46 GMT

Hi All,

I have the following expression, which I use in the extension called MinimalistHTMLTextBox. This extension acts like a marque and moves the text from left to right on the screen.

='<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' & ' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')' & ''

, '' & ' , ' & '' , -Aggr($(e.charge-amount), CUSTOMER))

The result works fine, however I always have blank brackets at the start of the string and I want to remove these

Whats the best way of removing these?

Regards

Alan

Re: remove first brackets on concatenated expression

sunny_talwar — Wed, 18 Apr 2018 09:03:24 GMT

May be this

='<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' &

If(Len(Trim(Aggr($(e.charge-amount),

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& ''

, '' & ' , ' & '' , -Aggr($(e.charge-amount), CUSTOMER))

Re: remove first brackets on concatenated expression

PrashantSangle — Wed, 18 Apr 2018 09:10:44 GMT

try below,

='<marquee behavior="scroll" direction="left">'

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0')

'' & ' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')' & ''

)

='<marquee behavior="scroll" direction="left">'

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0'))

'' & ' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')' & ''

)

Regards,

Re: remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 09:24:36 GMT

Thanks I have tried all 3 and they don't show anything.

Re: remove first brackets on concatenated expression

PrashantSangle — Wed, 18 Apr 2018 09:41:23 GMT

Is it giving you any error?? like syntax error just try it with out marquee field.

in stalwar1‌ expression might be required aggregation field.

If(Len(Trim(Aggr($(e.charge-amount),Customer)))>0,

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

Re: remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 09:48:45 GMT

Hi,

When I use it outside of the marquee it returns a blank text object.

Regards

Alan

Re: remove first brackets on concatenated expression

sunny_talwar — Wed, 18 Apr 2018 11:00:28 GMT

So, this isn't working (Note the part in blue which I missed earlier)

='<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' &

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& ''

, '' & ' , ' & '' , -Aggr($(e.charge-amount), CUSTOMER))

Re: remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 11:24:20 GMT

Yes, that's it, the only issue I have now is it is still showing the first comma.

Any ideas?

Re: remove first brackets on concatenated expression

sunny_talwar — Wed, 18 Apr 2018 11:25:57 GMT

Try one of these

=If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,

'<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' &

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

& ''

, '' & ' , ' & '' , -Aggr($(e.charge-amount), CUSTOMER)))

='<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' &

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,

''

, '' & ' , ' & '' , -Aggr($(e.charge-amount), CUSTOMER))))

Re: remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 11:32:47 GMT

GOT IT

='<marquee behavior="scroll" direction="left">'

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'' &

If(Len(Trim(Aggr($(e.charge-amount),CUSTOMER))) > 0,

' (' & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& ''

, '' & If(Len(Trim(Aggr($(e.charge-amount),CUSTOMER))) > 0,' , ' ,' ')& '' , -Aggr($(e.charge-amount), CUSTOMER))

Re: remove first brackets on concatenated expression

sunny_talwar — Wed, 18 Apr 2018 11:33:16 GMT

Or that

Re: remove first brackets on concatenated expression

rustyfishbones — Wed, 18 Apr 2018 12:01:25 GMT

Thanks Sunny.