Load in-line fromAPI

[dannylanckman]

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?

[Anonymous]

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/Sub...

[dannylanckman]

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!

[Anonymous]

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

[dannylanckman]

‌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.

[vikraant]

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/cros...

It would be helpful if you could provide sample data.

Best Regards,

Vikraant

[dannylanckman]

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.

[dannylanckman]

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