Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
dineshkumarl
Partner - Creator
Partner - Creator

Qlik Replicate | Store Changes | Capture only Changed Field

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

 

Labels (3)
2 Solutions

Accepted Solutions
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Heinvandenheuvel
Specialist III
Specialist III

@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

 

 

View solution in original post

7 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

>> 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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

@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
Partner - Creator
Partner - Creator
Author

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