Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Compare and store changes

I need some help with setting up a way to store historical data.

The datasource changes alot and unfortunatly source system doesnt save some changes.

Unfortunatly things are even hard-deleted from the source which means I want to also find/mark things in QV that they were deleted.

Today what I store looks basicaly like this:

Keyamountpricevalue1value2
1520BigSomething
22030HugeGoodbye

This is reloaded every night, which means I can always only see the current state of things.

And I want to be able to store compare it each day and store:

Keyamount
price
value1
value2
ActiveFrom
ActiveTo
Deleted
ActiveVersion
1520BigSomething2010-08-102012-10-05

1520SmallSomethingElse2012-10-06

1
22030HugeGoodbye2010-08-10
1

Anyone got a clue where I should start?

Do I have to manually compare every column of every table? or is there some kind of quicker compare?

basically I dont care what has changed, as soon as ANYTHING changes I want a new record for it.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi,

Could try using a composite key of all the fields you think will change:

    Key&'-'&Amount&'-'&Price&'-'&Value1&'-'&Value2 AS CompKey

You can then do the following series of steps:

1. load in your fresh data to TodaysTable

2. load in yesterday's data (stored off using a qvd from end of this process the day before) to YesterdaysTable

3. take from todaystable rows which have not changed using where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

4. add on rows that have changed, again using a where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

That would only account for those rows that were there before, or have changed slightly. As for new rows, you should be able to bring in those one from TodaysTable that didn't exist on yesterdays:

    WHERE NOT EXISTS (Key,YesterdaysKey);

View solution in original post

2 Replies
Anonymous
Not applicable
Author

Hi,

Could try using a composite key of all the fields you think will change:

    Key&'-'&Amount&'-'&Price&'-'&Value1&'-'&Value2 AS CompKey

You can then do the following series of steps:

1. load in your fresh data to TodaysTable

2. load in yesterday's data (stored off using a qvd from end of this process the day before) to YesterdaysTable

3. take from todaystable rows which have not changed using where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

4. add on rows that have changed, again using a where clause:

    WHERE EXISTS (CompKey,YesterdaysCompKey);

That would only account for those rows that were there before, or have changed slightly. As for new rows, you should be able to bring in those one from TodaysTable that didn't exist on yesterdays:

    WHERE NOT EXISTS (Key,YesterdaysKey);

Not applicable
Author

Had completely forgotten about this post

But Jonathan did get it right, to do something like this you need to load, reload reload reload the data building up one thing at a time. Creating a "checksum" for each ID helps alot.

I've now had this running without much trouble since around the time of the original post.

It is however a terrible time and resource-waster, and if you HAVE to do this then you should be asking source system to create a audit of some sort instead, but it is doable