Historization script - working, but not plausible

[datanibbler]

Hi,

I have a script that is intended to historize the data in one table in our database that is not historized in itself:

- The table holds different time_measurements for a combination of item_nr, customer and order_type

- Every time one such combination is assigned a new set of times, the old one is overwritten in the database

- All the table has is a Change_date - that is like the start_date of the validity of a new set of measurements.

I have a historization app that is built like any other - in short, it loads an existing history, appends the data from three plants with a WHERE NOT EXÍSTS() clause on a keyfield - made up of the original combination of 3 fields and the change_date.

=> In that new table, there is only a valid_from date. A valid_until is then calculated (as either the day before the begin_date of a newer record for that same combination or a date in the far future)

=> The new table is again stored to memory, to be checked again the next day.

I can attach my script, it's no secret. Putting together some fake data will be more of an effort, but I can get to it this afternoon.

The issue is, the script runs daily, without any errors, but - it runs for a year now and when I look at the field 'valid_until', there is only one value - that far_future_date => that means there is only one set of time_measurements for every combination, no change was ever recorded.

That is not very plausible. So I am searching for any potential error in the script now.

Can anyone help me to spot potential pitfalls in there?

Thanks a lot!

Best regards,

DataNibbler

[p_verkooijen]

Hi DataNibbler

When using WHERE NOT EXISTS, this keeps on beeing validated versus the data loaded.
Not just the new data vs the existing data.

So when a new combination has been loaded this combination "suddenly" exists in the dataset and no new records will be imported.

[datanibbler]

Hi Paul,

this sounds interesting. I'll have a look in the script and see if I can see what you mean.

There are three CONCATENATEs, right?

In the first one, I use two different fields in this WHERE NOT EXISTS clause - the field > Key_change < that exists in the history_file and the (combination of fields making up that key) coming from the file to load.

The second LOAD => The field Key_change now exists in both the history and the records from plant_#1 - but the second LOAD is from another plant, so that key cannot be the same between plants, so it could only possibly be the same as in the prior history.

Third LOAD - same story.

I still don't see why it doesn't work. There are quite some records coming from the first LOAD when I deactivate that clause, of course - can I maybe check that in another way, to monitor whether that clause does what it's supposed to?

I cannot check it against the database itself, of course, because that only ever has one record for every combination.

Thanks a lot!

Best regards,

DataNibbler

[giakoum]

Haven't be reading carefully, but maybe this helps :

The exists issue

[datanibbler]

Hi ioannis,

that is indeed an implication that it would be very easy to fall for. I wasn't really aware of that.

But unfortunately, that cannot be the error in this instance. The key is made up of the three fields for which a unique set of times exists plus the date it was changed - so it is unique since any new set of times inserted for that same combination of fields would have a new change_date. So the key is necessarily unique.

I am just checking that, however.

[datanibbler]

Hi,

I have now looked at the whole thing with a colleague.

Obviously, the historization in principle is working - we found one combination of the three principal key_fields where the change_date is different (six records), so that combination got a new set of time_measurements five times.

<=> The issue is, we have six different "valid_from" dates (simply the recorded change_date), but only one "valid_until" date, the latter being calculated - so the error can ONLY be in that formula.

I post the formula here (in the step before that, I have sorted the whole table by those three keyfields (with no suffix) and by change_date (with the suffix desc), so the newer set of times should now be above the old one.

LOAD

...

IF((%ITEM_NUMBER = PREVIOUS('%ITEM_NUMBER') AND %CUSTOMER = PREVIOUS('%CUSTOMER') AND %ORDER_TYPE = PREVIOUS('%ORDER_TYPE')), (PREVIOUS('CHANGE_DATE')-1), '31.12.2100') as Valid_until

<=> That seems to be right - if I have Jan. 29, 2015 and Jan 26, 2015, the newer one (from Jan 29) would be above the other.

=> That one (from Jan 29) would get Dec 31, 2100 as end_date since the formula wouldn't return TRUE.

=> The one from Jan 26 would get (Jan 29) -1 as end_date, that would be Jan 28, 2015

Only it obviously doesn't work that way.

I hope someone can see through this. I will try myself by building in the script the different elements of that function.

[datanibbler · Accepted solution]

Solved!

For whatever reason, using PREVIOUS() did not work - but using PEEK() does. Now I have six records, with the test returning TRUE five times and with six different valid_until dates.

I will just let it run to the end before closing this thread.