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 😉