Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Concatenated a table

Hi people,

I need to make a incremental charge, for that reason i load my qvd and load my new query, after i need to join only the results that doesn't exist on the qvd.

How can i exclude this values, cause Exist() could not use in diferent tables?

****load the qvd

CARGA_INCREMENTAL:

LOAD

*

FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);

***** get the latest update

DATA_ATUALIZACAO:

LOAD

timestamp(MAX(DATA_SERV),'DD/MM/YYYY hh:mm:ss') AS DATAMAX

RESIDENT CARGA_INCREMENTAL;

Let vDatamax=FieldValue('DATA_SERV',1);

Concatenate(CARGA_INCREMENTAL)

CARGA_INCREMENTAL2:

LOAD

ID_SYSTEM,

DATE_READ,

ORA_ROWSCN,

PORT,

VALUE_READ,

DATA_SERV,

CONCENTRATOR

WHERE DATA_SERV>'$(vDatamax)'

;

SELECT

id_system,

port,

date_read,

value_read,

concentrator,

ora_rowscn,

scn_to_timestamp(ora_rowscn) data_serv

from rs_individual_read

where   date_read between TO_DATE(SYSDATE) and TO_DATE(SYSDATE+1);

1 Solution

Accepted Solutions
eduardo_dimperio
Specialist II
Specialist II
Author

Hi everyone, i finally did. Follow the final code if someone need for something

CARGA_INCREMENTAL:

LOAD

*

FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);

Concatenate(CARGA_INCREMENTAL)

LOAD

ID_SYSTEM,

DATE_READ,

PORT,

VALUE_READ,

CONCENTRATOR,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA;

SELECT

id_system,

port,

date_read,

value_read,

concentrator,

ora_rowscn,

scn_to_timestamp(ora_rowscn) data_serv

from rs_individual_read

//where   ora_rowscn>timestamp_to_scn(SYSDATE-1) AND (date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

where   ora_rowscn>timestamp_to_scn(sysdate - interval '1' hour)

AND(date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

//this solved my problem

NoConcatenate

QVD_ATUALIZADO:

Load

Distinct

ID_SYSTEM,

DATE_READ,

PORT,

VALUE_READ,

CONCENTRATOR,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CARGA_INCREMENTAL;

Drop Table CARGA_INCREMENTAL;

View solution in original post

5 Replies
petter
Partner - Champion III
Partner - Champion III

I think that this line:

Let vDatamax=FieldValue('DATA_SERV',1);


Should be:


Let vDatamax=FieldValue('DATA_MAX',1);

jayaseelan
Creator III
Creator III

Hi ,

Try this for setting the max date,

Let vDatamax= Num(Peek('DATAMAX',0,'DATA_ATUALIZACAO'));

Thanks,

eduardo_dimperio
Specialist II
Specialist II
Author

Hi guys,

Petter is right about the field name, but im still with the same result 2 tables that i dont know how to concate.

But really thank's for help me

marcus_malinow
Partner - Specialist III
Partner - Specialist III

You've got a little bit of redundant code. Delete 'CARGA_INCREMENTAL2, as I presume this is overriding the prior Concatenate statement:

Concatenate(CARGA_INCREMENTAL)

CARGA_INCREMENTAL2:

LOAD

ID_SYSTEM,

eduardo_dimperio
Specialist II
Specialist II
Author

Hi everyone, i finally did. Follow the final code if someone need for something

CARGA_INCREMENTAL:

LOAD

*

FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);

Concatenate(CARGA_INCREMENTAL)

LOAD

ID_SYSTEM,

DATE_READ,

PORT,

VALUE_READ,

CONCENTRATOR,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA;

SELECT

id_system,

port,

date_read,

value_read,

concentrator,

ora_rowscn,

scn_to_timestamp(ora_rowscn) data_serv

from rs_individual_read

//where   ora_rowscn>timestamp_to_scn(SYSDATE-1) AND (date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

where   ora_rowscn>timestamp_to_scn(sysdate - interval '1' hour)

AND(date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

//this solved my problem

NoConcatenate

QVD_ATUALIZADO:

Load

Distinct

ID_SYSTEM,

DATE_READ,

PORT,

VALUE_READ,

CONCENTRATOR,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CARGA_INCREMENTAL;

Drop Table CARGA_INCREMENTAL;