CrossTab Audit Data

[jagjit_singh]

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

[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

[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

[jdunkerley79]

Attached fixed version.

You were truncating the time to 5 characaters. Once that was sorted no need for the parsers.

[jagjit_singh]

Does not work when i run the workflow against the actual table. Please see below the datatype and error

Jag

[jdunkerley79 · Accepted solution]

Ok try the attached,

I stopped being lazy and used a datetimeparse