Hi
I've created a small workflow. And my issue is:
I have two dataset's. Lastmile and linehaul.
They have two join fields in this sample dataset:
ScanDate and Delivery_Country_Code.
I want to match my lastmile dataset to my linehaul on these two.
If there is no linehaul on the same day as the lastmile, I want to take the previous linehaul.
As an example, the 19/9-22 I have lastmile data:
But no linehaul that day, so I want to itter through the linehaul and find the next one, irevious to that date:
Which would be the two linehaul the 16/9-22;
For my onwards calculations, it's important to me that I itter thorough the data, and not generate rows as an example to generate days between. So please only suggest solution containing itteration.
Kind regards,
Solved! Go to Solution.
Hi
Try the attached workflow.
Basically i generated extra rows for Linehaul ScanDate whenever there's a missing ScanData by taking the row from the earlier date.
This causes the dataset to be complete for your analysis i believe.
let me know if this works out for you.
Greetings,
Seb
I can see how you would do this with an iterative macro, by trying to match on the dates and subtracting one from unmatched dates until there are no rows left (or you reach a date below the minimum), but it would be complex to set up and not necessary for the problem you describe. My solution would be to rejoin the unmatched rows without the date criteria, filter out dates above the one you're matching, sort and sample to take the latest possible date.
One row is missing, where there are no earlier dates to match to.