Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Can you try to put words to the logic you want? That is often helpful when trying to find a solution.
My first impression of your query is that you will need to do some kind of comparison and select between the date_to value and the date_from value on the row above.
Helpful function to consider are peek(), rangemin()/rangemax() and maybe coalesce()/alt().
Yes, I would like to put the sentences into a time series by comparing the date from and the date to.
To get the "date_to" from the next "date_from-1" is not the challenge. I get this with peek().
My problem is how can i get the additional row "18.02.2024" - "07.03.2024" because the row from "29.01.2024" is only valid to 17.02.2024 and the next row is only valid from 08.03.2024. So i have to create a row with the last row 12.01.2024.
Yes, I would like to put the sentences into a time series by comparing the date from and the date to.
To get the "date_to" from the next "date_from-1" is not the challenge. I get this with peek().
My problem is how can i get the additional row "18.02.2024" - "07.03.2024" because the row from "29.01.2024" is only valid to 17.02.2024 and the next row is only valid from 08.03.2024. So i have to create a row with the last row 12.01.2024.
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
You need to apply the interrecord-stuff with peek() and previous() twice. The first one checked if a real and valid to-date exists and if this fits to it's previous from-date - if not you set a flag-value of 2 and otherwise it's 1.
The next step is performing a while-loop, like:
load * resident while iterno() <= MyFlag;
which adds an extra record for those records which have a date-gap and then the final from-to data are evaluated per interrecord-functions.
It may go in this direction whereby you may need some more if-loops especially for the extra records and/or further adjustments, for example the date-offset between the normal and extra records.
load *, if(product = previous(product), previous(from) - 1, ...) as to2; load * while iterno() <= flag; load *, if(product = previous(product) and to < '31.12.9999' and previous(from) - to > 1, 2, 1) as flag resident X order by product, from desc;
