topic Re: Load in-line fromAPI in Integration, Extension & APIs

Load in-line fromAPI

dannylanckman — Thu, 15 Feb 2018 13:05:47 GMT

Hello,

I have the folowing problem.

When I want to put an array of data from an API in qlik sense, but qlik sense put this al in 1 column.

I need to fill in this date in 1 row of 10 values from 1 array, and a second array for the 3 following values (Phase1;Phase 2;Phase 3) . Still in one line, then a second, third line with thesame structure.

so:

First column is the Time stamp, second till the 10th column : (CT1;CT2;CT3;CT4;CT5;CT6;CT7;CT8;CT9), 11th till 13th (Phase1;Phase 2;Phase 3)

The first column must be the timestamp, and the next is equal the the 9 values in the donwload column, but must be placed in a row.

An example in excel in attachment.

Who can help me?

Re: Load in-line fromAPI

Anonymous — Thu, 15 Feb 2018 17:47:17 GMT

If qliksense loads it in one field you could use the Subfield to split it the way you need

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/StringFunctions/SubField.htm

Re: Load in-line fromAPI

dannylanckman — Thu, 15 Feb 2018 21:34:37 GMT

Hi Josefina,

Creating the colums works, thanks.

My second problem is now that the values aren't loaded in line.

Still loaded in columns.

How can i solve this?

Thanks in advance!

Re: Load in-line fromAPI

Anonymous — Fri, 16 Feb 2018 00:42:46 GMT

¿How does QV show the data when only extracted from the source? (With no transformation)

Re: Load in-line fromAPI

dannylanckman — Fri, 16 Feb 2018 08:33:04 GMT

‌Hi

I use Qlik Sense, not Qlik view.

As you can see in the previous screenshot qlik sense load all the data in the first column.

WHat I need is to load this data in 1 row per 9 values.

Per 9 lines in the column the data must be placed in 1 row with the 9 values in 9 fields.

Next row with the next 9 values, and so further.

TThanks for your reply.

Re: Load in-line fromAPI

vikraant — Tue, 20 Feb 2018 04:48:09 GMT

Hi Danny ,

You could use Cross table function to load the columnar data in rows.

Check the examples in following link.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/crosstable.htm

It would be helpful if you could provide sample data.

Best Regards,

Vikraant

Re: Load in-line fromAPI

dannylanckman — Wed, 21 Feb 2018 00:22:04 GMT

Hi Pai,

Thanks for your reply, but I don't find the right answer in the help section.

The problem is that the data values "active","reactive" and the "voltages" need to be placed in columns next to the "consumption"column as CT1,CT2,CT3 till CT9. each row with 9 value and so on...(see column in screenshot)

The API from the supplier provides the data per timestamp in 1 array of 9 values, and the problem is that qlik sense download the [@value] in 1 column in stead of 9 columns without the timestamp.

See below the API call in bold

RestConnectorMasterTable:

SQL SELECT

"serviceLocationId",

"__KEY_root",

(SELECT

"timestamp",

"consumption",

"solar",

"alwaysOn",

"__KEY_consumptions",

"__FK_consumptions",

(SELECT

"@Value",

"__FK_active"

FROM "active" FK "__FK_active" ArrayValueAlias "@Value"),

(SELECT

"@Value" AS "@Value_u0",

"__FK_reactive"

FROM "reactive" FK "__FK_reactive" ArrayValueAlias "@Value_u0"),

(SELECT

"@Value" AS "@Value_u1",

"__FK_voltages"

FROM "voltages" FK "__FK_voltages" ArrayValueAlias "@Value_u1")

FROM "consumptions" PK "__KEY_consumptions" FK "__FK_consumptions")

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

WITH CONNECTION(

QUERY "aggregation" "2",

QUERY "from" "0",

QUERY "to" "9999999999999999",

HTTPHEADER "Authorization" "bearer $(vAccess_Token)");

[active]:

LOAD

num#([@Value] ,'0.0','.',',') AS [@Value],

[__FK_active] AS [__KEY_consumptions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_active]);

[reactive]:

LOAD

num#([@Value_u0] ,'0.0','.',',') AS [@Value_u0],

[__FK_reactive] AS [__KEY_consumptions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_reactive]);

[voltages]:

LOAD

num#([@Value_u1] ,'0.0','.',',') AS [@Value_u1],

[__FK_voltages] AS [__KEY_consumptions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_voltages]);

[consumptions]:

LOAD

MakeDate(1970, 1, 1 ) + ([timestamp] / (1000 * 60 * 60 * 24)+ '0,041667') as measuredate ,

[timestamp] AS [timestamp],

num#([consumption],'0.0','.',',') AS [consumption],

num#([solar] ,'0.0','.',',') AS [solar],

num#([alwaysOn] ,'0.0','.',',') AS [alwaysOn],

[__KEY_consumptions] AS [__KEY_consumptions],

[__FK_consumptions] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_consumptions]);

[root]:

LOAD [serviceLocationId] AS [serviceLocationId],

[__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

Thanks for your reply.

Re: Load in-line fromAPI

dannylanckman — Tue, 27 Mar 2018 14:13:09 GMT

Problem solved in collaboration with Agilos Belgium, Qlik developper and training center. Solved with a load inline and concatenate. Thanks to Diogo.