Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
eduardo_dimperi
Not applicable

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_dimperi
Not applicable

Re: Concatenated a table

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;

5 Replies
petter
Not applicable

Re: Concatenated a table

I think that this line:

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


Should be:


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

jayaseelan
Not applicable

Re: Concatenated a table

Hi ,

Try this for setting the max date,

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

Thanks,

eduardo_dimperi
Not applicable

Re: Concatenated a table

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
Not applicable

Re: Concatenated a table

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_dimperi
Not applicable

Re: Concatenated a table

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;