topic Concat Two Fields in QlikView

Concat Two Fields

Thu, 30 Jun 2011 13:17:13 GMT

Hi,

I'm trying to concatenate two fields in my load statement:

BIN:

LOAD Bin,

[Last Four],

concat( Bin&'*****'&[Last Four]) as Card Number

FROM

[Concat Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

My goal's to create a field 'Card Number' by combining 'Bin' and 'Last Four', however, I keep getting sytax errors when I try and load it. I've read a few posts on this and can't see the issue with my script. Any suggestions would be more than welcome.

Thanks in advance for the help.

Matt

Concat Two Fields

erichshiino — Thu, 30 Jun 2011 13:19:04 GMT

You don't need to use the concat in this case:

This should work

Bin&'*****'&[Last Four] as Card Number

Re: Concat Two Fields

swuehl — Thu, 30 Jun 2011 13:28:30 GMT

Hi Mattsies,

concat is defined as:

concat ( [ distinct ] expression, [delimiter [, sort-weight]] )

returns the aggregated string concatenation of all values of expression iterated over a number of records as defined by a group by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be determined by sort-weight. Sort-weight should return a numeric value where the lowest value will render the item to be sorted first. If the word distinct occurs before the expression, all duplicates will be disregarded.

So I think the syntax you used will not return a list of values to iterate over, that's why it causes an error.

Concat is used to e.g. get a comma separated string of values of a dimension like:

concat (DIMENSION, ',')

For example in a Text box or chart expression.

Cheers,

Re: Concat Two Fields

Thu, 30 Jun 2011 14:24:06 GMT

Thanks Erich.

When I edit the script according to your suggestion, I get the same error. Would you mind showing me how you'd do it with the attahced excel as an example?

Really appreciate the help.

Matt

Re: Concat Two Fields

Thu, 30 Jun 2011 14:25:14 GMT

Thanks Swuehl,

I will try and get it to work using Erich's syntax.

Matt

Re: Concat Two Fields

nagaiank — Thu, 30 Jun 2011 14:37:12 GMT

I used the following script and it worked for me.

LOAD *, Bin & '*****' & Num([Last Four],'0000') as Result;

LOAD Bin, [Last Four]

FROM "Concat Test.xlsx" (ooxml, embedded labels, table is Sheet1);

Re: Concat Two Fields

swuehl — Thu, 30 Jun 2011 14:37:37 GMT

Hi,

pls. add brackets [] around [Card Number]

regards,

Stefan

Re: Concat Two Fields

Thu, 30 Jun 2011 14:40:22 GMT

The brackets were critical! Thanks again for the help.

Matt

Re: Concat Two Fields

Thu, 12 Sep 2013 18:10:51 GMT

Very good this solution. Solved a problem using the.

Thanks

Stive