How to track records that have changes in some columns.

[jim_chan]

Hi guys,

I have posted up this question b4. but no answer yet.

For example . i have 2 years of data loaded in. 2015-2016.

Take ID 003 as example: in year 2015, category column is charlie and locatin column is shop

in year 2016, ID 003, category changes to delta and location change to Toilet.

I need a text object to count all these ID that has changed. and a table box that show which ID has changes in category and location column.

[sunny_talwar]

Is making changes in the script an option or everything needs to be front end?

[jim_chan]

That's great.!! if i would want to show it in a table box?

[jim_chan]

Bro, Duch has provided a solution from the back end.

Is it doable on the front end? but which one should be better . at teh front end of back end script?

Rgds,

Jim

[sunny_talwar]

Back end would be a static solution which won't change based on selection. On the other hand, front end will be dynamic but will take more resources. Depends on what you really need.

Best,

Sunny

[jim_chan]

Bro, mind to share or contribute your method too?

[sunny_talwar]

Here is a back end approach

Record_2015:

LOAD * INLINE [

    Year, Record_ID, Rating, Status, Category, Location

    2015, 001, 10, Active,Alpha,Mall

    2015, 002, 20, Active,Beta,Street

    2015, 003, 30, Active,Charlie,Shop

];

Record_2016:

LOAD * INLINE [

    Year, Record_ID, Rating, Status, Category, Location

    2016, 001, 10, Deleted, Alpha, Mall

    2016, 002, 10, Active, Beta, Street

    2016, 003, 30, Active, Delta, Toilet

    2016, 004, 40, Active, Echo, Car Park

];

FinalTable:

LOAD *,

  If(Record_ID = Previous(Record_ID),

  If(Category = Previous(Category) and Location = Previous(Location), Null(), 1), Null()) as Flag

Resident Record_2015

Order By Record_ID, Year;

DROP Table Record_2015;

[jim_chan]

i tot you gonna show front end apporach,bro.

[sunny_talwar]

You just said contribute your method

[jim_chan]

haha.. i was waiting for your front end, how would it look like. bro,

[sunny_talwar · Accepted solution]

Front end solution

Text box expression:

=Count(DISTINCT {<Record_ID = {"=Count(DISTINCT Category&Location) > 1"}>} Record_ID)

Straight table (instead of table box)

Dimensions

Record_ID

Year

Status

Rating

Location

Category

Expression

=If(Year = 2016, Count(DISTINCT {<Record_ID = {"=Count(DISTINCT Category&Location) > 1"}>} Record_ID))