Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Incremental Update

Hi everyone,

I need to create a qvd only with the new data, the problem is that new data could be some measure of today, or last month or any date.

So i thinked about get the last modified on DB. Someone knows how to do that with QS?

1 Solution

Accepted Solutions
eduardo_dimperi
Valued Contributor II

Re: Incremental Update

Rá ! I did it !

Follow the answer

CARGA_INCREMENTAL:

LOAD

ID_SYSTEM,

OID_METER,

DATE_READ,

ORA_ROWSCN,

DATA_E_HORA;

select i.id_system,i.ora_rowscn,m.oid_meter,date_read,scn_to_timestamp(i.ora_rowscn) data_e_hora

                      from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                      where m.oid_meter = c.oid_meter

                        and c.concentrator_counters = i.concentrator

                        and c.port_counters = i.port

                         and date_read between c.dt_init and c.dt_end

                        and date_read between m.dt_init and m.dt_end

                        and u.oid_uc=m.oid_uc

                        and u.oid_system=i.id_system

                        and i.id_system =20

                         and i.ora_rowscn > TIMESTAMP_TO_SCN(SYSDATE-5)

5 Replies
MVP
MVP

Re: Incremental Update

So i thinked about get the last modified on DB. Someone knows how to do that with QS?

You think right about last modified date. But what exactly are you asking?

eduardo_dimperi
Valued Contributor II

Re: Incremental Update

Hey Sunny !

i.e

I had a meter that was broken for a week, then it was fixed and start to send me measure again, so it will populate the DB with measure of today and the last 7 day, but if i load only date=today, i'll lose data of the last 7 days.

Check this out (little big code), i want to load only data that had some modified. (I dont know with QS has some way to check the last modification)


CONSUMO_CONDOMINIOS:

LOAD

    ADDRESS_BLOCK_UC,

    ADDRESS_COMPLEMENT_UC,

    NAME_UC,

    PENDENCY_COUNT,

    DESCRIPTION_METER,

    SERIAL_NUMBER_METER,

    OID_METER,

    ID_SYSTEM as OID_SYSTEM,

    TYPE_METER,

    GROUP_TYPE_METER,

    TYPE_UNIT,

    EXTERNAL_KEY_UC,

    DATEINI,

    DATEFIM,

    [DATEFIM]-[DATEINI] AS PERIODO,

    VALUE_READ_INI,

    VALUE_READ_FIM,

    CORRINI,

    CORRFIM,

    LEITURAINI,

    LEITURAFIM,

    LEITCORRINI,

    LEITCORRFIM,

    CONSUMO;

SQL SELECT

       

        si.address_block_uc address_block_uc

        ,si.address_complement_uc address_complement_uc

        ,si.name_uc name_uc

        ,pendency_count

        ,si.description_meter description_meter

        ,si.serial_number_meter serial_number_meter

        ,si.oid_meter oid_meter

        ,si.id_system id_system

        ,si.name_type_meter type_meter

        ,si.group_type_meter group_type_meter

        ,si.type_unit type_unit

        ,si.external_key_uc

        ,date_read_ini dateini

        ,date_read_fim datefim

        ,value_read_ini value_read_ini

        ,value_read_fim value_read_fim

        ,corr_factor_ini corrini

        ,corr_factor_fim corrfim

        ,leitura_ini leituraini

        ,leitura_fim leiturafim

        ,leitura_ini + corr_factor_ini leitcorrini

        ,leitura_fim + corr_factor_fim leitcorrfim

        ,(leitura_fim + corr_factor_fim) - (leitura_ini + corr_factor_ini) consumo

    from (select u.address_block_uc, u.address_complement_uc, u.name_uc, u.pendency_count

                ,u.external_key_uc, m.description_meter, m.serial_number_meter

                ,tm.name_type_meter,tm.group_type_meter,m.type_unit

                ,m.oid_meter, lst.id_system

                ,lst.min_date_read date_read_ini

                ,ini.value_read value_read_ini

                ,lst.max_date_read date_read_fim

                ,fim.value_read value_read_fim

                ,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.min_date_read ) + (get_correction_pulse(lst.id_system,cini.concentrator_counters,cini.port_counters,lst.min_date_read)*m.type_constant) corr_factor_ini

                ,nvl(f.first_measur,0) + ( ini.value_read * m.type_constant ) leitura_ini

                ,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.max_date_read ) + (get_correction_pulse(lst.id_system,cfim.concentrator_counters,cfim.port_counters,lst.max_date_read)*m.type_constant) corr_factor_fim

                ,nvl(f.first_measur,0) + ( fim.value_read * m.type_constant ) leitura_fim

              from (select id_system, m.oid_meter,

                           min(date_read) min_date_read, max(date_read) max_date_read

                      from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                      where m.oid_meter = c.oid_meter

                        and c.concentrator_counters = i.concentrator

                        and c.port_counters = i.port

                         and date_read between c.dt_init and c.dt_end

                        and date_read between m.dt_init and m.dt_end

                        and u.oid_uc=m.oid_uc

                        and u.oid_system=i.id_system

//                         and u.oid_system= 581

                         and i.date_read between TO_DATE(SYSDATE-3)

                                                 and TO_DATE(SYSDATE+1)

                    --    and m.individual_flag=?

                      group by id_system, m.oid_meter

                   ) lst

                 inner join rs_meter m

                       on (m.oid_meter = lst.oid_meter

                           and m.oid_meter = lst.oid_meter

                           and ( lst.min_date_read between m.dt_init and m.dt_end

                                or lst.max_date_read between m.dt_init and m.dt_end

                               )

                          )

                 inner join rs_counters cini

                       on (cini.oid_meter=m.oid_meter

                           and ( lst.min_date_read between cini.dt_init and cini.dt_end )

                          )

                 inner join rs_counters cfim

                       on (cfim.oid_meter=m.oid_meter

                          and ( lst.max_date_read between cfim.dt_init and cfim.dt_end )

                          )

                 inner join rs_individual_read ini

                       on (ini.id_system = lst.id_system

                           and ini.date_read = lst.min_date_read

                           and ini.concentrator = cini.concentrator_counters

                           and ini.port = cini.port_counters)

                 inner join rs_individual_read fim

                       on (fim.id_system = lst.id_system

                           and fim.date_read = lst.max_date_read

                           and fim.concentrator = cfim.concentrator_counters

                           and fim.port = cfim.port_counters)

                 inner join rs_uc u

                       on (u.oid_system = lst.id_system

                           and u.oid_uc = m.oid_uc)

                 inner join rs_type_meter tm

                       on (m.oid_type_meter = tm.oid_type_meter)

                 left outer join rs_first_measurs f

                       on (f.oid_meter = m.oid_meter)

         ) si order by address_block_uc,address_complement_uc,name_uc,group_type_meter,type_meter,serial_number_meter;

STORE CONSUMO_CONDOMINIOS INTO [lib://MI_csv/CONSUMO_CONDOMINIOS.qvd];

eduardo_dimperi
Valued Contributor II

Re: Incremental Update

Guys,

I did a test that i believe is the answer, I'll do more tests to be sure about it

CARGA_INCREMENTAL:

LOAD

ID_SYSTEM,

OID_METER,

DATE_READ,

ORA_ROWSCN;

select i.id_system,i.ora_rowscn,m.oid_meter,date_read

                      from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                      where m.oid_meter = c.oid_meter

                        and c.concentrator_counters = i.concentrator

                        and c.port_counters = i.port

                        and date_read between c.dt_init and c.dt_end

                        and date_read between m.dt_init and m.dt_end

                        and u.oid_uc=m.oid_uc

                        and u.oid_system=i.id_system

                        and i.id_system =20

                        and i.date_read between TO_DATE(SYSDATE-1)

                                                and TO_DATE(SYSDATE)

eduardo_dimperi
Valued Contributor II

Re: Incremental Update

Well,

It not work like i want.

If i Try

CARGA_INCREMENTAL:

LOAD

DATA_E_HORA;

select  id_system,scn_to_timestamp(ora_rowscn) data_e_hora

from                     

          rs_individual_read

I receive that error

Ocorreram os seguintes erros:

SQL##f - SqlState: S1000, ErrorCode: 8181, ErrorMsg: [Oracle][ODBC][Ora]ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

It happen cause scn_to_timestamp get only changes in the last 120 hours, when my select return a ora_rowscn bigger then 120 hours, occur this message. For that reason the first code runs without problem, but no retrieve the right information to me.

Any Idea?

eduardo_dimperi
Valued Contributor II

Re: Incremental Update

Rá ! I did it !

Follow the answer

CARGA_INCREMENTAL:

LOAD

ID_SYSTEM,

OID_METER,

DATE_READ,

ORA_ROWSCN,

DATA_E_HORA;

select i.id_system,i.ora_rowscn,m.oid_meter,date_read,scn_to_timestamp(i.ora_rowscn) data_e_hora

                      from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c

                      where m.oid_meter = c.oid_meter

                        and c.concentrator_counters = i.concentrator

                        and c.port_counters = i.port

                         and date_read between c.dt_init and c.dt_end

                        and date_read between m.dt_init and m.dt_end

                        and u.oid_uc=m.oid_uc

                        and u.oid_system=i.id_system

                        and i.id_system =20

                         and i.ora_rowscn > TIMESTAMP_TO_SCN(SYSDATE-5)