Qlik Replicate | Store Changes | Capture only Changed Field

[dineshkumarl]

Hi All,

I am running one store changes task with config to capture before and after changes.

Data which I receive:

forexample:

Before Data: col1: 1, col2:ABC, col3:12A

After Data: col1: 1, col2:ABC, col3:12B

Is there way to get only the changed field in Before and After Data

what I need:

Before Data: col3:12A

After Data: col3:12B

Irrespective of the column, if col2 and col3 has been changed then at result I should have only col2,col3 before and after data.

 Kindly assist. Thanks

[john_wang]

Hello @dineshkumarl ,

Thanks for reaching out to Qlik Community!

Yes, we may filter the changes columns/records by using $BI__ , see User Guide Using a column's before-image data in a transformation.

Hope it helps.

John.

[Heinvandenheuvel]

>> Is there way to get only the changed field in Before and After Data

No.

Why? Auditing?

What is thesource & target ?  There may be end point specific options/restrictions.

You can figure it out, but it requires non-trivial SQL coding against the changes table.

Specifically one needs to learn all about : [header__]change_mask - varbinary (128)
The change mask indicates which data columns in the change table
are associated with columns that changed in the source table. 

Do you need to know the changes values, or just the fact it changed? The latter is easier.

What do you need for insert (null + all?) and delete (all + null?)

And one could also 'compare' BI and AI column by column vor each event.

Search the Replicate doc for changeMask, change_Mask  and columnmask

Hein.

[dineshkumarl]

Hi Hein,

Yes, it's for Auditing. My Source is oracle and target is Kafka. I have enabled header_change_mask before posting question in my task. I either see null values or random character in that column.

Thanks

[dineshkumarl]

Hi John,

As per your information, I have created one new column and added expression $BI__$mycolumn in expression builder. However, I do not see any changes post that for that specific table. Let me know if I am missing something here.

Thanks

[john_wang · Accepted solution]

Hello @dineshkumarl ,

I greatly agree with @Heinvandenheuvel , if it's hard to 'remove' the un-changed columns then let's keep all columns in the target table but create a view and use 'changeMask, change_Mask  and columnmask' to 'hide' the un-changed columns.

Regards,

John.

[Heinvandenheuvel · Accepted solution]

@dineshkumarl  ". I either see null values or random character in that column."

Did you read the documentation?

That's not random - of course it is not, nothing is random - it is just a difficult to interpret bit mask indicating which columns changes as per documentation.

>>  have created one new column and added expression $BI__$mycolumn in expression builder. However, I do not see any changes post that for that specific table

You would have to do so for each and every column and subsequently compare BI and AI.

My advice to you is to go back to the business folks and explain this it can be done but it is a sh*t load of work. Are they ready to pay for that, or can they review their needs and clarify exactly what is needed at what time intervals. Maybe they only care about 2 or 3 columns but failed to say so?

Maybe the only need a weekly report and you can just use the change tables to compare.

Maybe the don't need anything except the re-assurance that you can research for a particual username or object ID on request.

So much of auditing data is never ever looked at in reality, that there is no need to make a nice and tidy structure for it. It will take 4 manweek to create such structure and it might only take an hour to investigate an incident without structure. Their choice!

Hein

[dineshkumarl]

Thank you for the information. I will review and work on it accordingly.