
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You don't need to use the concat in this case:
This should work
Bin&'*****'&[Last Four] as Card Number


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Swuehl,
I will try and get it to work using Erich's syntax.
Matt


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
pls. add brackets [] around [Card Number]
regards,
Stefan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The brackets were critical! Thanks again for the help.
Matt

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Very good this solution. Solved a problem using the.
Thanks
Stive
