|
|
Rank: Member
Joined: 2/2/2021
Posts: 10
Location: United States
|
Hi There -
This forum was really helpful a few weeks ago for me so I thought I'd throw another question out there I need to solve.
I have a column in excel that can have either one or multiple values in it. When there are multiple values they will be comma separated (or any separation. I can control this part)
Example:
I have this ALMOST working the way I need it to (although I'll need to do this slightly differently later in the XML that I'll probably need some assistance with too)
Each value (including the 101, 201 and 202) need to be represented ONCE in the XML output. Very similar to an issue I was helped with previously.
I'm close to a solution right now but instead of the 101, 201 and 202 showing up as distinct values it's just showing up as 101201202. I think I'm using tokenize wrong.
Here's my setup and current output.
SETUP:
OUTPUT:
The output is correct for the Excel rows above. I only want each value represented once in <Assignment_Level_3>
The issue is that when I tried to use tokenize with (,) as delimiter it's just removing the commas and jamming the data together (the 101201202)
Desired result is two more nodes for 201 and 202 since those don't exist.
Any help would be greatly appreciated!
Thanks,
Shawn
|
|
Rank: Advanced Member
Joined: 6/16/2006
Posts: 486
Location: AT
|
Are you sure that your cell doesn't contain the value of 101201202, which is 101 millions, 201 thousands and 202?
Maybe try a different separator.
|
|
Rank: Member
Joined: 2/2/2021
Posts: 10
Location: United States
|
that wrote:Are you sure that your cell doesn't contain the value of 101201202, which is 101 millions, 201 thousands and 202?
Maybe try a different separator.
You're right. That's exactly how excel was treating 101,201,202. I feel silly for not realizing that. I used a semicolon as the delimiter instead. The data in excel now reads 101;201;202
The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now.
Anything else you can think of? I'm plugging away at this in my spare time but this is a hard requirement on the mapping I'm going to have to do eventually. I had luck in the forum last time so I thought I'd try here again before I employ Altova's consulting services.
Thanks!
Shawn
|
|
Rank: Advanced Member
Joined: 6/16/2006
Posts: 486
Location: AT
|
ShawnL wrote:The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now.
What should happen here is that the line with multiple room numbers is added to multiple groups. So you should have separate elements for 101, 201, and 202 that are grouped together with other rows having those values. I don't see 201 and 202 on your screenshot, but that's maybe just because the bottom is cut off.
|
|
Rank: Member
Joined: 2/2/2021
Posts: 10
Location: United States
|
that wrote:ShawnL wrote:The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now. What should happen here is that the line with multiple room numbers is added to multiple groups. So you should have separate elements for 101, 201, and 202 that are grouped together with other rows having those values. I don't see 201 and 202 on your screenshot, but that's maybe just because the bottom is cut off.
Nope, that's the problem. The 201 and 202 aren't showing up at all unfortunately.
Shawn
|
|
Rank: Advanced Member
Joined: 6/16/2006
Posts: 486
Location: AT
|
It works for me. Make sure that you define column G ("Room#") in the Excel component as type string and not numeric as it might have been before.
|
|
Rank: Member
Joined: 2/2/2021
Posts: 10
Location: United States
|
that wrote:It works for me. Make sure that you define column G ("Room#") in the Excel component as type string and not numeric as it might have been before.
BEAUTIFUL!
You're right. I had it defined as numeric. When I started this I was told that there would only be one value in the cell. So I defined that field as numeric. Obviously my problem.
Thank you SO MUCH.
On to the next problem now. They have to be defined again, one node for each of the values in the cell further down in our schema. I'm going to attempt it myself before I reach back out.
Seriously, Thank you for your help!
Shawn
|
|
|
guest |
