Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

j_schmidt
Contributor

Concatenating data from two REST connections

Hi,

i can't make it work to concatenate data from two REST connections.

Both my REST connections are valid and return a valid JSON response


The problem ist the following:

The data of the second connection ist not beeing concatenated to the data table before.

It doesnt matter here which REST connection comes first in the order of the script.

It is always the data of the first connection which is loaded.

Maybe somebody can tell me what i'm doing wrong?

This is my script:

-----------------------------------------------------------------------------------------------------------------------

// Data from first REST connection

LIB CONNECT TO 'REST connection 1';

RestConnectorMasterTable:

SQL SELECT

     "__KEY_root",

    "field1",

    ...

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATA]:

LOAD

     ...   

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

// Data from second REST connection

LIB CONNECT TO 'REST connection 2';

RestConnectorMasterTable:

SQL SELECT

     "__KEY_root",

    ...

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD

     ...       

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

9 Replies
agigliotti
Honored Contributor II

Re: Concatenating data from two REST connections

could you show your data model?

j_schmidt
Contributor

Re: Concatenating data from two REST connections

Hi Andrea,

as i understand the concatenation the data model is not relevant, ist it?

I thought the data ist just added to the first table?

agigliotti
Honored Contributor II

Re: Concatenating data from two REST connections

to understand better your problem I'd need to see the whole script at least, not partial as shown above.

j_schmidt
Contributor

Re: Concatenating data from two REST connections

Well, i just commented out the columns to have a better overview.

This is my complete script:

---------------------------------------------------

LIB CONNECT TO 'ES_proxy_izchecker - BASISDATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "checkergebnis",

    "dateiname",

    "dateityp",

    "status",

    "system",

    "time_id"

  

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATEN]:

LOAD

     [checkvorgang_id] AS [Checkvorgang-ID],

     [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [checkergebnis] AS [Checkergebnis],

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

     [status] AS [Status],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp

   

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ES_proxy_izchecker - TXT_DATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "dateiname",

    "dateityp",  

    "system",

    "time_id",

    "HANDLE",

    "BLOCKNAME",

    "MAIN_INFO",

    "DEVICE_NUMBER",

    "GROUP",

    "ADD_INFO",

    "ADD_INFO2",

    "COUNTER",

    "ROOM_NUMBER",

    "AREA",

    "CABLES"

  

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD

[checkvorgang_id] AS [Checkvorgang-ID],

    [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp,   

    [HANDLE] AS handle,

    [BLOCKNAME],

    [MAIN_INFO],

    [DEVICE_NUMBER],

    [GROUP],

    [ADD_INFO],

    [ADD_INFO2],

    [COUNTER],

    [ROOM_NUMBER],

    [AREA],

    [CABLES]

       

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

agigliotti
Honored Contributor II

Re: Concatenating data from two REST connections

if you want to concatenate the two tables , do as below:

LIB CONNECT TO 'ES_proxy_izchecker - BASISDATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "checkergebnis",

    "dateiname",

    "dateityp",

    "status",

    "system",

    "time_id"

FROM JSON (wrap on) "root" PK "__KEY_root";

[DATEN]:

LOAD

     [checkvorgang_id] AS [Checkvorgang-ID],

     [auftrag_id] AS Auftrag_ID,

     [benutzer_id] AS Benutzer_ID,

     [checkergebnis] AS [Checkergebnis],

     [dateiname] AS [Dateiname],

     [dateityp] AS [Dateityp],

     [status] AS [Status],

    [system] AS [Laufsystem],

    [time_id] AS [time_id],

    date(

      date#([time_id], 'YYYYMMDDhhmmss'),

      'DD.MM.YYYY - hh:mm:ss'

    ) AS Timstamp

 

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LIB CONNECT TO 'ES_proxy_izchecker - TXT_DATEN';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

    "checkvorgang_id",

    "auftrag_id",

    "benutzer_id",

    "dateiname",

    "dateityp",

    "system",

    "time_id",

    "HANDLE",

    "BLOCKNAME",

    "MAIN_INFO",

    "DEVICE_NUMBER",

    "GROUP",

    "ADD_INFO",

    "ADD_INFO2",

    "COUNTER",

    "ROOM_NUMBER",

    "AREA",

    "CABLES"

FROM JSON (wrap on) "root" PK "__KEY_root";

concatenate

LOAD * resident [DATEN];


drop table DATEN;

j_schmidt
Contributor

Re: Concatenating data from two REST connections

Hi Andrea,

thanks for your reply.

I tried that and i get an error.

I dont quite understand the logic either though.

You basically change the tabele i am concatenating from in the second connection, right?

'RESIDENT [DATEN];'

Why do it want to concatenate data from resident [DATEN]?

I loaded the data from the second connection in RestConnectorMasterTable?

agigliotti
Honored Contributor II

Re: Concatenating data from two REST connections

yes the script concatenate your second connection table with the first one.

what error do you get ?

j_schmidt
Contributor

Re: Concatenating data from two REST connections

The error is:

Field 'checkvorgang_id' not found

agigliotti
Honored Contributor II

Re: Concatenating data from two REST connections

related to the first or second sql select?

Community Browser