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.

XLSX only searching for first match, not the rest? Options · View
Ash
Posted: Tuesday, June 15, 2021 12:06:18 PM
Rank: Member

Joined: 8/21/2019
Posts: 10
Location: United Kingdom
Hi,

I've been provided with a text file which I have split into a CSV using FlexText. This appears to be all ok.

The source of the file is also the output - but with some string manipulation in between (typical MapForce stuff).

I'm attempting to search a specific field from the text file, and match it against the XLSX file... If it matches, then another field should be changed.

The logic seems simple - but for some reason, it only appears to be searching the first row of the XLSX file? But I can't work out why?

Here are a couple of screenshots showing what I mean.

First screenshot shows my logic:



Second screenshot shows the layout of the XLSX file



Third screenshot shows the output - showing that the criteria has been changed correctly... But only for the first code found in the XLSX list



Fourth screenshot shows the output - showing that the criteria hasn't been changed... Even though the 'code' is the 2nd in the list.

that
Posted: Tuesday, June 15, 2021 2:07:14 PM
Rank: Advanced Member

Joined: 6/16/2006
Posts: 459
Location: AT
When you run the mapping, are you getting any warnings in the Messages window, like "value was overwritten"?

You may need to add "filter" and "exists" components (between "equal" and "if") so that you produce only one output value per input, not one per Cartesian product of input value and lookup rows.
Ash
Posted: Tuesday, June 15, 2021 4:31:12 PM
Rank: Member

Joined: 8/21/2019
Posts: 10
Location: United Kingdom
Not quite that exact message, but similar

"Multiple values were mapped to Tariff Rate Identified (or TRID)."
"Multiple values were mapped to Tariff Rate ID (or TRID)."

I've tried connecting 'Filter' in a couple of places, but I can't seem to get the right combo... It still only seems to match the first record from the XLSX.
All others (although they match) are not 'changing'?

I connected the 'Filter' function to the parent node, and this resulted in all 'matches' appearing - but then anything that didn't match completely disappeared.

I feel like this is close...

I'm making the incorrect connections to 'Filter'? I'm not to sure how to utilize the 'Exists' function.

If I upload the files, would you mind taking a look? Only if you have time of course.

Thanks

[EDIT] I've added a link to the download. Here https://bit.ly/3vrEEOP
that
Posted: Tuesday, June 15, 2021 5:29:03 PM
Rank: Advanced Member

Joined: 6/16/2006
Posts: 459
Location: AT
Ash wrote:
I've tried connecting 'Filter' in a couple of places, but I can't seem to get the right combo...


It's easy if you think in small steps:

The "A" output from your Excel table will return ALL numbers in your list.
You want to know if at least one value EXISTS in that list that is EQUAL to the value from your input file.

So you first FILTER the values from your Excel table, using the condition that it is EQUAL to the value from your input file.

The output of the filter will now contain all matching values - zero if there is no match, and - given that they are unique - at most one.
To decide whatever you want to do in your "if-else" components, you want to know if the filter result EXISTS.

So you connect the output of the filter to an "exists" function and the result of that function to your existing "if-else".
Ash
Posted: Tuesday, June 15, 2021 7:35:31 PM
Rank: Member

Joined: 8/21/2019
Posts: 10
Location: United Kingdom
Got it! Thank you very much.

Sometimes it either takes a simple explanation or some time away from the screen to understand where you're going wrong.

Just in case anyone else has similar issues, this was the solution.



Thanks again for your help!
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.