Rank: Member
Joined: 2/2/2021 Posts: 10 Location: United States
|
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
|
Rank: Advanced Member
Joined: 6/16/2006 Posts: 486 Location: AT
|
Quote: I get one <Assignment_Level_3> node per unique "Room #" in the excel sheet.
So what you actually want is to *group* your Excel rows by "Room #". Use the "group-by" function instead of distinct-values, and put it in between "Rows" and "Assignment_Level_3". Then, use first-items to retrieve the first Location in each group to fill Assignment_Level_2.
P.S: "replicate-sequence" with a count of 1 should not have any effect.
|
Rank: Member
Joined: 2/2/2021 Posts: 10 Location: United States
|
Hi That -
That worked!
Thank you so much! Have a great rest of your week.
Shawn
|