Problem with concat function in an expression

[#1]

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?

[JonnyPoole]

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

[MarcoWedel]

please post sample app.

thanks

regards

Marco

[#4]

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?

[JonnyPoole]

Ha !  simple (my mistake)

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

[#6]

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

OTHER,

OTHER,BC,

OTHER,BC,AUTO,

Any way to supress that?

[JonnyPoole]

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.

[#8]

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.

[JonnyPoole · Accepted solution]

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.

replace( replace( trim( replace(InsID2,' ','|')& ' ' & replace(InsID3,' ','|')&' ' & replace(InsID4,' ' ,'|') &' ' & replace(InsID5,' ','|')),' ',','),'|',' ')

[#10]

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!