Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
j_schmidt
Creator
Creator

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
Partner
Partner

could you show your data model?

j_schmidt
Creator
Creator

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
Partner
Partner

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

j_schmidt
Creator
Creator

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
Partner
Partner

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
Creator
Creator

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
Partner
Partner

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

what error do you get ?

j_schmidt
Creator
Creator

The error is:

Field 'checkvorgang_id' not found

agigliotti
Partner
Partner

related to the first or second sql select?