topic Re: Problem with concat function in an expression in QlikView

Problem with concat function in an expression

Fri, 26 Sep 2014 15:41:19 GMT

I have a straight table with 3 dimensions: TransactionID, Account, Date

I want to concatentate the following four fields, separated by commas, but omitting the comma if the field is NULL.

InsID2

InsID3

InsID4

InsID5

Using concat( InsID2&''&InsID3&''&InsID4&''&InsID5, ', ' ) should work, but I get inconsistent results.

For example, where InsID2 = OTHER, and all others are NULL, I see the following results:

OTHER

OTHER, OTHER

OTHER, OTHER, OTHER

OTHER, OTHER, OTHER, OTHER

Sometimes, where InsID2 = WC and InsID3 = BC, it returns WCBC, omitting the comma.

Also, where all 4 fields are NULL, the commas still appear sometimes (but not always).

Am I missing something in the syntax of this expression?

Re: Problem with concat function in an expression

JonnyPoole — Fri, 26 Sep 2014 15:45:31 GMT

Not sure if you want concat(). How about this ?

InsID2 & if( isnull(InsID2)<>-1,',') & InsID3 & if( isnull(InsID3)<>-1,',') & InsID4 & if( isnull(InsID4)<>-1,',') & InsID5 & if( isnull(InsID5)<>-1,',')

It will be a different result every row in the chart

Re: Problem with concat function in an expression

MarcoWedel — Fri, 26 Sep 2014 16:10:03 GMT

please post sample app.

thanks

regards

Marco

Re: Problem with concat function in an expression

Fri, 26 Sep 2014 16:51:31 GMT

Thanks, Jonathan. That solved the errors. The only remaining issue is how to eliminate the comma that appears at the end of each created string. Any thoughts?

Re: Problem with concat function in an expression

JonnyPoole — Fri, 26 Sep 2014 16:53:26 GMT

Ha ! simple (my mistake)

InsID2 & if( isnull(InsID2)<>-1,',') & InsID3 & if( isnull(InsID3)<>-1,',') & InsID4 & if( isnull(InsID4)<>-1,',') & InsID5

Re: Problem with concat function in an expression

Fri, 26 Sep 2014 16:59:02 GMT

Forgive my miscommunication. There's a comma at the end of every line:

OTHER,

OTHER,BC,

OTHER,BC,AUTO,

Any way to supress that?

Re: Problem with concat function in an expression

JonnyPoole — Fri, 26 Sep 2014 17:23:52 GMT

The solution to this issue would involve too many IFs so i different approach may work better.

Try this:

= replace( trim(InsID2& ' ' & InsID3&' ' & InsID4&' ' & InsId5),' ',',')

it should work great, except if the InsID values could have spaces within the value.

Re: Problem with concat function in an expression

Fri, 26 Sep 2014 17:27:50 GMT

It does work! But, as you said, there are sometimes spaces in the field value. I know how to take care of this in Excel, but can't quite get it right in Qlikview.

Re: Problem with concat function in an expression

JonnyPoole — Fri, 26 Sep 2014 17:41:20 GMT

hmm... yes i was afraid of that. There could be a quick tweak , but only if you know of a character that would NEVER be in the VALUE. Here i picked the '|' character. I replace spaces with |'s in each value and then at the end put the spaces back in. Would you have |'s in your values ?:) I can't say i'm completely satisfied with it . We should be able to get to a 100% guarantee. Anyways let me know what your comfortable with.

Re: Problem with concat function in an expression

Fri, 26 Sep 2014 17:52:56 GMT

We have a winner!

Can't say that I understand how it works, but I'll study it.

Thanks very much for your help, Jonathan!