7 Replies Latest reply: Mar 27, 2018 10:13 AM by Danny Lanckman RSS

    Load in-line fromAPI

    Danny Lanckman

      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
          Josefina Fasoli

          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
              Danny Lanckman

              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?Exemple.png

              Thanks in advance!

                • Re: Load in-line fromAPI
                  Josefina Fasoli

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

                    • Re: Load in-line fromAPI
                      Danny Lanckman

                      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 Koushika Pai

                          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
                              Danny Lanckman

                              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)

                              Exemple.png

                              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.