Read and store the most updated data from a qvd

[luansantos]

Hello, I'm trying to read a qvd file with several history records, in this example I have the person's name, somatic fields, and the date of the last update.

I would like to read this file and store in another qvd only the most up-to-date data, based on the dt_modification field.

I currently do this in the database using the following SQL:

select ha.*

  from reg_history ha

                join (

      select id_people

                    , max(dt_modification) as dt_modification

        from reg_history

          group by id_people

            ) AS t ON t.id_people = ha.id_people and t.dt_modification = ha.dt_modification

order by ha.id_people;

Now i am trying to convert this sql to qliksense scriptload,can anybody help me? with this code we can make a script for clean duplicate records in QVD based on last modification data.

wrong scriptload example:

[table1]:

load ha.*  from FROM ha [lib://FOLDER (qlikserver_unknow)/HISTORY.QVD](qvd)

            join (

select id_people, max(dt_modification) as dt_modification

        from reg_history

          group by id_people

           ) AS t ON t.id_people = ha.id_people and t.dt_modification = ha.dt_modification

order by ha.id_people;

STORE [table1] INTO [lib://FOLDER (qlikserver_unknow)/HISTORY_V2.QVD](qvd);

drop table [table1];

EXIT SCRIPT;

[vishsaggi]

May be this?

SQLTable:

SQL

select id_people,

       max(dt_modification) as dt_modification

from   reg_history

group by id_people;

GetMaxDate:

LOAD Max(dt_modification) AS MaxDate

Resident SQLTable;

LET vMaxDate = Peek('dt_modification');

Drop Table GetMaxDate;

[table1]:

load * 

from [lib://FOLDER (qlikserver_unknow)/HISTORY.QVD](qvd)

WHERE dt_modification >= $(vMaxDate);

STORE [table1] INTO [lib://FOLDER (qlikserver_unknow)/HISTORY_V2.QVD](qvd);

drop table [table1];

EXIT SCRIPT;