Hi,
Consider a Third-Party Logistics (3PL) company with multiple warehouses (Sheds).
Their clients are sometimes moved from one Shed to another.
For reporting purposes I need to determine which Shed a client is in for each month. I can do this by processing the Jobs Transactions report (MYOB).
However, some clients may not have a transaction every month. When this occurs I want to insert a new row in my data for each of these missing months
Attached is a file (CustomerShedLocationByMonth.xlsx) with 3 tables. Incoming data, Dates table and the End Result required.
I hope the Dates table doesn't throw anyone off. It's come about due to 445 date handling. EOM Date means End Of Month date.
The Dates table has been included because I think it may be possible to Merge it with the data and using Group and/or Sort somehow to get the missing months into the output.
See if you can follow what I've don in the attached file. Since I couldn't work with your own queries as they connect to your Sharepoint folder, I added two small queries choosing a different approach.
The end result is what you specified and should work on large data sets as well. I didn't use ether the Date Table or the ClientDate key column.
Fantastic Riny.
The file shouldn't have had any queries in it at all. Sorry about that. (Think I copied a table)
Your answer was exactly what I was looking for and really helped.
I learnt two new concepts here.
First, I'd never realised you can insert a query into an AddColumn call.
Second, the idea of Merging a query with an earlier stage of the same query had never occured to me before.
Riny, your an AFL. (Thats the Aussie TLA for Absolute Legend.)
