Hi, I have an audit table that stores the before and after values as data is changed on the front end. I want to transform the audit data as below PropcodeTenantCodeStartDateEndDate10113284423/12/201016/05/201410115096711/06/201411/06/201410115103310/07/201415/07/201410115112013/08/2014 I had an attempt but not sure how to transform the data to get the above. I have attached the workflow Thanks Jag TenancyStartEnd.yxzp

CrossTab Audit Data

Hi,

I have an audit table that stores the before and after values as data is changed on the front end. I want to transform the audit data as below

Propcode	TenantCode	StartDate	EndDate
1011	32844	23/12/2010	16/05/2014
1011	50967	11/06/2014	11/06/2014
1011	51033	10/07/2014	15/07/2014
1011	51120	13/08/2014

I had an attempt but not sure how to transform the data to get the above. I have attached the workflow

Thanks

Jag

TenancyStartEnd.yxzp

Transformation

Preparation

Accepted answers

jdunkerley79

Ok try the attached,

I stopped being lazy and used a datetimeparse

TenancyStartEnd.yxmd

All comments

jdunkerley79

Had a quick play.

Taking the raw audit and converting into stream of events for each PropCode, FieldID.

THen selecting the tennant code and finaly reshaping to required result.

I made assumption all columns updated together which I appreciate may not always be true

Hopefully enough to start with

TenancyStartEnd.yxzp

jagjit_singh

Thanks. That worked fine with the xls file but when i attached it to my actual database table, creattime field created using the forumula tool gives the error below as the date and time field are V_string in the original table. I used the parse tool to convert the date and time string fields but that did not help.

Thanks

Jag

TenancyStartEndV1.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703