Solved: Re: Clone the last record if there is no record as... - Qlik Community

Ribeiro · ‎2024-06-04

I need to create a script that clones table 2 with the result

table 2

In example table 2 it generated records that are missing as of today.

Neves

Vegar · ‎2024-06-05

Imprtant that your table is read ordered by cod and date (desc).

from_to:

LOAD cod, date as valid_from, If(Peek('cod') = cod, Peek('valid_from')-1, today()) as valid_to, balance

RESIDENT Data

ORDER BY cod, date DESC;

Assuming you have a table containing all the "date" values you are interested in (if not then you will need to create it) .

Intervalmatch:

Intervalmatch (date)

LOAD valid_to, valid_from RESIDENT from_to;

Vegar · ‎2024-06-04

You could try to generate a table with a to and from field. Like below.

Load cod, date as valid_from, peek(date) as valid_to, balance

From source

Order by cod, date desc;

Once you have this table the use interval match on the date field in a transaction table containing all dates or master calendar.

Ribeiro · ‎2024-06-04

I haven't used qlik for a long time, can my friend create an example for me. thank you from the bottom of my heart🤔

Neves

Vegar · ‎2024-06-05

Imprtant that your table is read ordered by cod and date (desc).

from_to:

LOAD cod, date as valid_from, If(Peek('cod') = cod, Peek('valid_from')-1, today()) as valid_to, balance

RESIDENT Data

ORDER BY cod, date DESC;

Assuming you have a table containing all the "date" values you are interested in (if not then you will need to create it) .

Intervalmatch:

Intervalmatch (date)

LOAD valid_to, valid_from RESIDENT from_to;

Ribeiro · ‎2024-06-05

The logic was correct, but I would like to clone if there was no record in the current month. There's a way my friend can see if he can do it for me.

Neves

Clone the last record if there is no record as of today.