I am trying to set up a QV load script with a join function.
I have two tables: TabA (Master) and TabB.
TabA contains a Productnumber and a Date when this item has been purchased. Tab B contains Productnumber and a From_Date as well as an Until_Date (when product has been "on sale"). I would like now to check whether an item has been purchased while it was on sale and if yes, add the corresponding From and Until Dates.
The problem so far is, that it is possible that an item has been purchased twice, once in the "On sale" period and once outside. I just want to add the From and Until dates for the purchase where it was on sale (the other one gets blanked fields). Additionally an item can be "On sale" multiple times.
TabA: Item 1, Purchased on the 1st March and 1st April
TabB: Item 1, on sale between the 1st Feb and 15th Feb as well as between the 25th March and 10th April
Item 1, 1st March, -, -
Item 1, 1st April, 25th March, 10th April
I hope this is more or less clear, I would appreciate any help as I can't think of any solution that's working. I tried with a double left join and where clauses, but unfortunately it somehow adds the From and Until date on every line, rather than just on the corresponding one.