| that |
| Advanced Member |
|
| AT |
|
|
| None Specified |
|
| Friday, June 16, 2006 |
| Tuesday, June 15, 2021 5:31:18 PM |
451
[2.54% of all post / 0.08 posts per day] |
|
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".
|
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.
|
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.
|
|
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.
|
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.
|
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.
|
|
Add a filter component in the connection to Carrier with the condition that ID exists (use the exists function).
|
Streaming is always enabled, but if the mapping needs to access input data in a non-sequential way, it may still need to keep all data in memory. This depends on the structure of your schemas and your mapping. The Advanced Edition tries harder to optimize access patterns.
|
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.
|
You need lang.now, lang.datetime-add and a constant "-P15D". Maybe also lang.date-from-dateTime if you want to cut off the time.
|
|
