Log In
Register
Home
October 27, 2020
Offline
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.
Moderators
January 31, 2022
Offline
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.
October 27, 2020
Offline
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.)
1 Guest(s)
