|
|
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.
|
|
Rank: Advanced Member
Joined: 6/16/2006
Posts: 487
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.
|
|
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
|
|
Rank: Advanced Member
Joined: 6/16/2006
Posts: 487
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".
|
|
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!
|
|
|
guest |
