7 Replies Latest reply: Sep 13, 2018 7:22 AM by Shahzad Ahsan RSS

    Where clause in REST API script

    Shahzad Ahsan

      Hi Friends

      I have rest api script. I need to put where conditions to restrict data. How is this possible??

      The script is as follows

       

      SQL SELECT

      "key" AS "key_u3",

      "name" AS "name_u2",

      "__KEY_root",

      (SELECT

      "key" AS "key_u2",

      "name" AS "name_u1",

      "__KEY_folders",

      "__FK_folders",

      (SELECT

      "key" AS "key_u1",

      "name" AS "name_u0",

      "__KEY_folders_u0",

      "__FK_folders_u0",

      (SELECT

      "key" AS "key_u0",

      "timeZone",

      "surveyType",

      "__KEY_surveys",

      "__FK_surveys",

      (SELECT

      "key",

      "name",

      "locale",

      "__FK_question"

      FROM "question" FK "__FK_question"),

      (SELECT

      "start",

      "end",

      "__FK_activePeriods"

      FROM "activePeriods" FK "__FK_activePeriods")

      FROM "surveys" PK "__KEY_surveys" FK "__FK_surveys")

      FROM "folders" PK "__KEY_folders_u0" FK "__FK_folders_u0")

      FROM "folders" PK "__KEY_folders" FK "__FK_folders")

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

       

       

       

      I need to put where clause somewhere in script  like this

      Where "key_u2"='12345'

        • Re: Where clause in REST API script
          Bram Knuever

          you can put this on the bottom before the ";"

          exact syntax you will need to lookup for the SQL.

           

          Or use your Qlik Load statement and use it there.

            • Re: Where clause in REST API script
              Shahzad Ahsan

              I have tried in Qlik Load Statement, but its not working

                • Re: Where clause in REST API script
                  Bram Knuever

                  could you post the code?

                    • Re: Where clause in REST API script
                      Shahzad Ahsan

                      RestConnectorMasterTable:

                      SQL SELECT

                      "key" AS "key_u3",

                      "name" AS "name_u2",

                      "__KEY_root",

                      (SELECT

                      "key" AS "key_u2",

                      "name" AS "name_u1",

                      "__KEY_folders",

                      "__FK_folders",

                      (SELECT

                      "key" AS "key_u1",

                      "name" AS "name_u0",

                      "__KEY_folders_u0",

                      "__FK_folders_u0",

                      (SELECT

                      "key" AS "key_u0",

                      "timeZone",

                      "surveyType",

                      "__KEY_surveys",

                      "__FK_surveys",

                      (SELECT

                      "key",

                      "name",

                      "locale",

                      "__FK_question"

                      FROM "question" FK "__FK_question"),

                      (SELECT

                      "start",

                      "end",

                      "__FK_activePeriods"

                      FROM "activePeriods" FK "__FK_activePeriods")

                      FROM "surveys" PK "__KEY_surveys" FK "__FK_surveys")

                      FROM "folders" PK "__KEY_folders_u0" FK "__FK_folders_u0")

                      FROM "folders" PK "__KEY_folders" FK "__FK_folders")

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

                       

                       

                      [question]:

                      LOAD [key] AS [key],

                      [name] AS [name],

                      [locale] AS [locale],

                      [__FK_question] AS [__KEY_surveys]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__FK_question]) ;

                       

                       

                       

                       

                      [activePeriods]:

                      LOAD [start] AS [start],

                      [end] AS [end],

                      [__FK_activePeriods] AS [__KEY_surveys]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__FK_activePeriods]);

                       

                       

                       

                       

                      [surveys]:

                      LOAD [key_u0] AS [key_u0],

                      [timeZone] AS [timeZone],

                      [surveyType] AS [surveyType],

                      [__KEY_surveys] AS [__KEY_surveys],

                      [__FK_surveys] AS [__KEY_folders_u0]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__FK_surveys]);

                       

                       

                       

                       

                      [folders]:

                      LOAD [key_u1] AS [key_u1],

                      [name_u0] AS [name_u0],

                      [__KEY_folders_u0] AS [__KEY_folders_u0],

                      [__FK_folders_u0] AS [__KEY_folders]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__FK_folders_u0])  ;

                       

                       

                       

                       

                      [folders_u0]:

                      LOAD [key_u2] AS [key_u2],

                      [name_u1] AS [name_u1],

                      [__KEY_folders] AS [__KEY_folders],

                      [__FK_folders] AS [__KEY_root]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__FK_folders])   and [key_u2]='12345';

                       

                       

                       

                       

                      [root]:

                      LOAD [key_u3] AS [key_u3],

                      [name_u2] AS [name_u2],

                      [__KEY_root] AS [__KEY_root]

                      RESIDENT RestConnectorMasterTable

                      WHERE NOT IsNull([__KEY_root]);

                       

                       

                       

                       

                      DROP TABLE RestConnectorMasterTable;

                      • Re: Where clause in REST API script
                        Shahzad Ahsan

                        This is giving the result correct but when I select any filter then data is showing wrong.  Without selecting filters data is correct