Forum Discussion

pxg638's avatar
pxg638
Frequent Visitor
9 years ago
Solved

merging tables on two columns

Hi there,    First post, so apologies if this is super dumb - I was either searching for the wrong thing or being exceedingly dense in understanding what others had written! I have a large (1m row)...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    pxg638

     

    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's avatar
Anonymous
Not applicable
9 years ago

Hi pxg638

 

Could you provide column headers? I want to re-create your problem in Power BI to test a solution, I have in mind.

 

Thanks in advance.

 

Best

Martin 

pxg638's avatar
pxg638
Frequent Visitor
9 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    The example is just fine. I had missed the headers, so thank you for providing them. I'll re-create this in Power BI now, and try out my solution. Then I'll get back to you with the results.

     

    Best

    Martin

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

     pxg638

     

    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.

     

    • pxg638's avatar
      pxg638
      Frequent Visitor
      9 years ago

      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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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?