| ShawnL |
| Member |
|
| United States |
|
|
| None Specified |
|
| Tuesday, February 2, 2021 |
| Wednesday, March 3, 2021 3:22:20 PM |
10
[0.06% of all post / 0.01 posts per day] |
|
that wrote:ShawnL wrote:I need a set of functions that will eventually divide the number in the quantity cell by the number of delimited values. tokenize, count, divide - all in the core library.
Count.... that's what I was missing. I think I even tried it but fed the wrong input on the count function.
Thank you again very much.
This map is finished (until they tell me the client has completely changed how they want to do things of course).
This is a pretty major feature set that we're rolling out for our ERP product and I want to thank you for all the help you've given me. Very much appreciated.
Have a great rest of your week!
Shawn
|
Hi There!
I've got semicolon delimited data in an excel cell.
Example: 201;202;203
Then I've got a quantity value in another cell.
Example: 6
I need a set of functions that will eventually divide the number in the quantity cell by the number of delimited values. In this case the desired output I want to feed into my XML node would be 2. it would be 6 / 3 since there are three values (201, 202 and 203).
I've solved all of my other issues in this complex mapping with generous help from this forum and good ol' fashioned trial and error trying to learn MapForce.
Thank you in advance for your help!
Shawn
|
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
|
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
|
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
|
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
|
Hi That -
That worked!
Thank you so much! Have a great rest of your week.
Shawn
|
Hi There -
So I was able to solve the first piece of my problem yesterday but I didn't expect the problem to get more complicated at the time. Story of my life.
Anyways, I'm going to attempt to convey my problem succinctly. I appreciate any help in advance, even if you're just reading.
I'm going to show two bits of data from my Excel Source.
 
So I have this Room and Location columns. This data in both these columns repeats on many rows in the source Excel file.
Our XML schema requires that this data be defined, once per occurrence at the beginning of the schema before it can be used per row further down in the schema.
The key piece of data is Room # and Location (plus other data) is defined underneath the Room# in the Schema. The Room # is the most specific data while Location is less specific. Every instance Room # will ALWAYS have the same Location. In the case of this data, anytime Room # = 101 the Location will be 1st Floor. This is true for the other data that I'm not showing here.
In our schema, which I'm about to show refers to this data as Assignments. We have 3 levels.
Room = Assignment Level 3.
Location = Assignment Level 2.
We'll just focus on those since I can finish the rest of the data in the Assignments node after i get this solved.
Here's what I've got so far in MapForce
The green function works. I get one <Assignment_Level_3> node per unique "Room #" in the excel sheet.
I know why the red section doesn't work. It's just the most recent function that I tried to get my desired results.
Here's what I'm getting right now:
The desired result is that <Assignment_Level_2> underneath <Assignment_Level_3>'s for 201, 202, 203, etc be set to "2nd Floor", not "1st Floor".
When I tried using the distinct-values and replicate-sequence functions that i used in the green highlighted area I got this, which is also no good of course
That's basically it. I really appreciate any help you can give with my problem and even if you can't I greatly appreciate you reading through this post.
Cheers!
Shawn Luginbuhl
|
Hi There -
Please disregard...
I ended up figuring out how to do it with distinct-values & replicate-sequence. I'm not even sure the replicate-sequence is necessary but it's working so I'm not going to complain for now.
Thank you if you ended up reading!
Shawn
|
Hi There -
Looking for some help on accomplishing something in MapForce.
Let's say I have a column with a bunch of values in it. A lot of those values are repeated but the rest of the data I'm mapping to XML is different.
Example:
Row Headers: Item / Vendor / Cost / Price / Assignment
1010 / McKinney / 10.00 / 20.00 / 101
1011 / McKinney / 10.00 / 20.00 / 101
1012 / Sargent / 20.00 / 30.00 / 101
1013 / Sargent / 30.00 / 40.00 / 102
1014 / Sargent / 40.00 / 60.00 / 102
The important bit is the Assignment. I need to feed those into two places in the XML map. In the first place they can only exist once. I can handle the second place they need to go, that's easy.
Is there a way to Map from Excel to XML, the Assignment column but only once for each occurrence of a value?
I want to feed 101 and 102 into an XML node but only once even though 101 and 102 exist in more than one row of the excel data. Is this possible using any of the MapForce functions?
Excuse me, I'm a bit of a MapForce amateur and I thought I'd ask here to see if there was an easy solution before I logged a support ticket.
Thank you so much for Reading!
Shawn Luginbuhl
|
|
