Correct period

[reporting_neu]

Hi,

I have a valid from and a valid to date here. Unfortunately, the fields were not properly maintained, which is why the time periods overlap.

I would like to correct this in the script. Do you have an idea how I can do this the easiest way?

Example:

part	valid from	valid to	price
500	11.12.2020	31.12.2021	100
500	01.01.2022	31.12.2022	100
500	31.01.2022	31.12.2022	105
500	01.08.2022	31.08.2022	115
500	01.08.2022	30.09.2022	115
500	01.09.2022	30.09.2022	130
500	01.10.2022	31.12.2023	130

Correct:

part	valid from	valid to	price
500	11.12.2020	31.12.2021	100
500	01.01.2022	30.01.2022	100
500	31.01.2022	31.12.2022	105
500	01.08.2022	31.08.2022	115
500	01.08.2022	31.08.2022	115
500	01.09.2022	30.09.2022	130
500	01.10.2022	31.12.2023	130

[NiTo]

Hi, 

you can use the if function.

If(FieldName = 'OldValue', 'NewValue', FieldName) AS FieldName

and to delete the value from a field,first load your data and use the following script:

NoConcatenate
LOAD
*
WHERE NOT Exists(FieldName, 'ValueToDelete')
;

Hope it will work.

[reporting_neu]

Thanks for your answer.

You need to be a little more specific about that.

First, I checked whether the "valid to" date matched the previous record. If so, set a flag.

if(previous(part) = part and previous(valid_to) = valid_to, 1, 0) as Flag

Then I marked the data sets with it.

In the next step, I then have to take the pre-value (valid from) -1 from the affected data records.

Like:
if(previous(part) = part and Flag = 1, previous(valid_from)-1, valid_to) as valid_to

For this, however, the data records must all be sorted correctly. You would then have to do the same for Valid from. But that doesn't work 100% and is very time-consuming. So I'm looking for a more logical and simple solution.