Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concat Two Fields

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

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

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);

View solution in original post

8 Replies
erichshiino
Partner - Master
Partner - Master

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

This should work

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

swuehl
MVP
MVP

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,

Not applicable
Author

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

Not applicable
Author

Thanks Swuehl,

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

Matt

nagaiank
Specialist III
Specialist III

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);

swuehl
MVP
MVP

Hi,

pls. add brackets [] around [Card Number]

regards,

Stefan

Not applicable
Author

The brackets were critical! Thanks again for the help.

Matt

Not applicable
Author

Very good this solution. Solved a problem using the.

Thanks

Stive