Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist 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
Highlighted
Specialist II
Specialist II

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)

View solution in original post

5 Replies
Highlighted

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?

Highlighted
Specialist II
Specialist II

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];

Highlighted
Specialist II
Specialist II

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)

Highlighted
Specialist II
Specialist II

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?

Highlighted
Specialist II
Specialist II

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)

View solution in original post