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 😉