IMPORTANT:
this is not a Support Forum! Experienced users might answer from time to time questions posted here. If you need a professional and reliable answer, or if you want to report a bug, please contact Altova Support instead.

Excel to XML - Repeated Data in Cells to Single XML node - New issue Options · View
ShawnL
Posted: Wednesday, February 3, 2021 3:19:59 PM
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

that
Posted: Wednesday, February 3, 2021 3:47:07 PM
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.
ShawnL
Posted: Wednesday, February 3, 2021 4:26:50 PM
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
Users browsing this topic
guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Use of the Altova User Forum(s) is governed by the Altova Terms of Use.