REST connector not work, alternative options?

[NZFei]

Hi,

Here is the problem. Our customer maintains their HR data in web. They have some staff codes like 1, 2, 3 and some like 01, 02, 03 so they made the staff codes to text format. When I use the REST connector coming with Sense 3.2 SR4 to load the data, with the same script, sometimes I get all the staff codes with the format 1, 2, 3, sometimes I get all the staff codes with the format 01, 02, 03. Clearly this doesn't work for me as 1 and 01 are two staffs not one.

I have created a case but Qlik support cannot help as I am not allowed to share the user name and password to the web data source. They cannot reproduce the case.

My question is that is there any other options when we need to load data from web for json format for example. There is an extension called Generic REST or something but it doesn't work.

Can anyone give any idea please?

Thank you very much!

Cheers.

Fei

[felipedl]

Hi Fei,

For the 1 and 01 issue, Qlik uses the first interpretation as default, for example

x:

Load * Inline

[

  A,B

  01,a

  1,b

]

will get you something like

A   B

01 a

01 b

and

x:

Load * Inline

[

  A,B

  1,a

  01,b

]

will get you

A B

1  a

1  b

in this case, you could use the text() function in the field A as it retains the original format of text, not interpreting as a number and giving two different values (1 and 01 as strings).

Text ‒ QlikView

For the REST part, you can use the connector for that, you would have to see how to get the data out of the API they provide, since I can't access it though, but the connector is pretty straight forward.

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.0/Introduction/REST-...

I've tested with the following link for the rest connector:

https://jsonplaceholder.typicode.com/posts

LIB CONNECT TO 'REST Test'; // 'REST Test' is a simple Data Connection with the above URL

RestConnectorMasterTable:

SQL SELECT

  "userId"

FROM JSON (wrap on) "root";

[root]:

LOAD [userId] AS [userId]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

It would really depend on how the API your accessing works

Hope it helps,

Felipe.

[NZFei]

Hi Felipe,

Thank you so much for your reply!

I have used the text function but it did work.

If I use the text function here:

RestConnectorMasterTable:

SQL SELECT

  text("userId") as userId

FROM JSON (wrap on) "root";

I have this error:

If I use the text function after the data has been loaded, so here:

[root]:

LOAD text([userId]) AS [userId]

RESIDENT RestConnectorMasterTable;

It doesn't help as I have had the problem (so both 1 and 01 are showing either 1 or 01).

Is it possible to re-produce the data in your example to have some user IDs like 1, 2, 3 and some like 01,02,03 and you can explain how to make it work?

Thank you again.

Cheers.

Fei

[felipedl · Accepted solution]

Hi Fei,

It would have to be something like this I belive,

Root:

LOAD

text([userId]) AS [userId],

  [id] AS [id],

  [title] AS [title],

  [body] AS [body];

SQL SELECT

  "userId",

  "id",

  "title",

  "body"

FROM JSON (wrap on) "root";

I'm currently out of options to test with a REST API, but that works with the following script using a sample excel (attached).

Root:

LOAD

  text([userId]) AS [userId],

  [id] AS [id],

  [title] AS [title],

  [body] AS [body];

LOAD

    "userId",

    id,

    title,

    body

FROM [lib://Desk/Sample\Teste.xlsx]

(ooxml, embedded labels, table is Plan1);

Qlik software tries to infere the data type of what's being loaded (as Excel moslty does) so if you load a table the two fields '01' and 1 will be interpreted as numbers. The text() function lets the field as a string representation, so that you can have difference between the two values.

[NZFei]

Thank you very much Felip. That actually solved my problem.

What happened is that by default, if we insert script from REST connector, it will load the data using SQL select first, and then do another resident load.

When loading the raw data using SQL, it will understand all the data that looks like numbers as numbers and store into RAM. In my case, either 3 or 03 has been stored into RAM for both staff code 3 and 03. When I use text function in the resident load, it can not change the result.

If we do a load on load as you have suggested, the data will be manipulated during running time and then be stored into RAM. So the text function will work like this:

Load

    *,

    text(StaffCode) as StaffCodeInText;

SQL Select

    whatever

from REST connector;

Anyway that is my understanding about why your script works but the default REST connector script does not.

Thank you again!

Cheers.

Fei

[felipedl]

Glad it help Fei .