Forum Discussion

AOD's avatar
AOD
Icon for Helper III rankHelper III
6 years ago
Solved

New table creation /transformation

Hi All, 

 

I have a audit table which stores changes done at field level, would like to transform into new table to store the latest changes with old and new value as below.

Need help on how can this be achieved. Also please advice if any other method to achieve this instead of creating new table.

Thanks in advance. 

 

Existing Audit table 

EmployeeField 1Modified date 
EP123Yes22-Jun-20
EP123No23-Jun-20
EP123Yes24-Jun-20
EP124Yes22-Jun-20
EP124No23-Jun-20
EP125Yes21-Jun-20
EP125No 22-Jun-20
EP125Yes23-Jun-20
EP125No24-Jun-20

 

New table to be created - 

EmployeeNew ValueOld ValueLast modified date
EP123YesNo24-Jun-20
EP124NoYes23-Jun-20
EP125NoYes24-Jun-20

Thanks. 

Regard,

AOD

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    AOD , Try both as new columns

     last date = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] <earlier([Modified date])),[Modified date])
 last status = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] =earlier([last date])),[Field 1])

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    AOD , Try both as new columns

     last date = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] <earlier([Modified date])),[Modified date])
 last status = maxx(filter(Table, [Employee] =earlier([Employee]) && [Modified date] =earlier([last date])),[Field 1])

     

    • AOD's avatar
      AOD
      Icon for Helper III rankHelper III
      6 years ago

      Thanks Amit for solution.

       
       

      I have below output now. Is there as way that I can just keep last work of every employee in the table

       

      Thanks for your help.

      AOD