Forum Discussion

sc4ry's avatar
sc4ry
Helper I
6 years ago

Split Table and Re-Join

Dear all,   after hours of trying I finally resigned as I did not do any step forward =/   I am even not sure if I really need to Split and Re-Join the table, but I have a working model in KNIME ...
Need Help
Tips and Tricks
  • sc4ry's avatar
    sc4ry
    Helper I
    6 years ago

    Hey,

     

    the difference between our tables is exactly my issue. My data looks like the following:

     

    Employee Period HC Change

    A 12/19 +1

    B 01/20 +1

    C 01/20 +1

    A 02/20 -1

    C 03/20 -1

     

    Out of this I want to create a table like you have.

    Employee Entry Period Exit Period

    A 12/19 02/20

    C 01/20 03/20

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi sc4ry ,

       

      1, Go to edit queries and split your table into 3 columns:

      2.Then you need 2 measures as below:

       

      Hiring = CALCULATE(MIN('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),'Table'[Column3]=1)
      leaving = 
CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),FILTER('Table',DATEDIFF(MAX('Table'[Column2]),'Table'[Hiring],MONTH)<6 &&'Table'[Column3]=-1))

       

      3.Finally you will see:

       

      For the related .pbix file,pls click here.

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!

       

      • sc4ry's avatar
        sc4ry
        Helper I
        6 years ago

        Thanks a lot. I already started but not yet trough with the validation, but it looks promosing. I will come back again, at least to check the solution as far I am really sure 😉