Issue with a Join - where it worked already in a similar setting ...
Hi,
I have a scenario which is very similar to the one I had already and which I have described in the thread "complex sorting scenario":
- There is a list of packing_plans.
- Ideally, a key (made up of three elements) should be unique to one packing_plan, which has, among others, a transfer_date (whenever one of the packing_plans is replaced by a successor, that should be flagged and the flag should be removed for all
older ones, but it's not always the case)
=> In some cases, there are two packing_plans sharing one key, one of which is newer acc. to the transfer_date
=> So what I did is this (and this approach worked fine in another very similar scenario:
- I have a ReSIDENT LOAD where I sort by that three-elements-key (in asc. order) and by the transfer_date (in desc. order)
=> so now I have the last transferred packing_plan for every key as the first record of that group;
- Then I create (in that same LOAD) a field using a PREVIOUS() query to find out which records are the first in their resp. key_group;
- Then I have another RESIDENT LOAD where I use a WHERE clause to filter for only the records that have that flag I just created.
- Last, I want to use an INNER JOIN with the original table, joining on both the key and the transfer_date, to throw out any excess
outdated packing_plans and keep, for every key, only the newest one.
In theory, this should work fine (I used a loop instead of that INNER JOIN first, but it took ages) and it did in that other scenario - but it doesn't work in this one - after the INNER JOIN, there are still several packing_plans (in one example I have picked out), the situation is unchanged.
Can you think of any reason why it wouldn't have the same effect this time?
Hi, not sure, maybe the Join is trying to duplicate records, can you try the mapping version?
In this Step: "- Then I have another RESIDENT LOAD where I use a WHERE clause to filter for only the records that have that flag I just created."
You can change that to create the mapping table, with the Key and the date that should return and load again the first table using ApplyMap to set the date.
Now I readed it more slowly.. if I understood it right maybe it will be easier to create the table with the keys+transfer_date to keep and do a reload of the original table using "Where Exist(...)" to check the key.
thanks! For starters, I will have a closer look at the scenario where I used just that logic a few days ago and the one I tried yesterday - there must be something I did differently.
I think I'm through with this. I'm not sure what I did wrong yesterday, but now it worked - well, now I temporarily built a new key that had more elements than the original one to do the final INNER JOIN. I guess that was the difference.
If any new issues turn up, I'll post them here. Otherwise I'll close this thread today.
