Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (3)
1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Concat Two Fields

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

8 Replies
erich_shiino
Honored Contributor

Concat Two Fields

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

This should work

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

MVP
MVP

Re: Concat Two Fields

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

Re: Concat Two Fields

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

Re: Concat Two Fields

Thanks Swuehl,

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

Matt

nagaiank
Valued Contributor III

Re: Concat Two Fields

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

MVP
MVP

Re: Concat Two Fields

Hi,

pls. add brackets [] around [Card Number]

regards,

Stefan

Not applicable

Re: Concat Two Fields

The brackets were critical! Thanks again for the help.

Matt

Not applicable

Re: Concat Two Fields

Very good this solution. Solved a problem using the.

Thanks

Stive

Community Browser