Forum Discussion
merging tables on two columns
did you guys solve it with SQL after all?
If so, could I ask you to accept a solution for this post, so others can benefit from it anyway?
Anonymous
Column headers failed to show on the original post, sorry! HTML failed me! So you have reporting date, volume, and unit for the first table, then configuration date, unitname, fraction T1 work and fraction T2 work (effectively) for the 2nd table.
I am simplying the reality here, but the principle is presented.
Alright, the way, I've solved this, is by generating an ID-column for linking a certain production to a certain configuration. I'm not sure whether this will accommodate your dataflow or data-setup. If you can't generate an ID-column, I suggest you tell us a little more about your challenge and data-setup, so I can figure out a suiting solution for you. The smoothest way, in my opinion, to go about your challenge is to generate an ID-column. Take a look a below picture.
Let me know if this solves your problem, and if we should go in a different direction.
Anonymous
So that would work if the configuration table had an entry for every month that I had data. Right now, it doesn't, it only logs the changes. Using a [unit]&[Date.Month]&[Date.Year] key would enable this simply enough, if I had an entry for each and every month in both tables. Using that approach right now yields a huge number of null rows in the subsequent merge.
Maybe I misunderstand your reply, in which case please correct me. But you could probably duplicate configuration table for each month? It's just a new duplication when you guys have changes in the configuration table?
An example: Check whether there is any changes to configuration table: If there is none, duplicate last month, if there is changes duplicate those changes. Wouldn't this accommodate your problem?
Changing the source data set is harder to do than it should be. Would it be possible to duplicate those rows in a table within the analysis?