Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
jonbrough
Valued Contributor

Re: Compare and store changes

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);

2 Replies
jonbrough
Valued Contributor

Re: Compare and store changes

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

Re: Compare and store changes

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