Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Partner
Partner

Where statement in Qlik Rest Connector , Incremental Load

Hi,
I have a Rest Connection to a Raven DB. Restconnector is using Nested  select statements

How can I make a incremental load. Just loading new data?

Where to put "where-statement"?

RestConnectorMasterTable:
SQL SELECT
"IsStale",
"IndexTimestamp",
"TotalResults",
"SkippedResults",
"IndexName",
"IndexEtag",
"ResultEtag",
"NonAuthoritativeInformation",
"LastQueryTime",
"DurationMilliseconds",
"ResultSize",
"__KEY_root",

   
    (SELECT
  "OrderNumber",
  "Created",
  "CreatedLocalTime",
  "OrderStatus",
  "TransferStatus" AS "TransferStatus_u0",
  "CustomerStatus",
  "BasketId",
  "Approved",
  "Suppliers",
  "SupplierOrderReferences",
  "Responsible",
  "InternalOrderInfos",
  "MarginTotal",
  "ApprovedBy",
  "PreApproveMailSent",
  "UserId",
  "IsImported",
  "VisitorId" AS "VisitorId_u0",
  "SalesPerson" AS "SalesPerson_u0",
  "SendBasketGuid",
  "LastPriceValidation",
  "CreatedOn" AS "CreatedOn_u0",
  "TaxTotal",
  "TotalQuantity",
  "IsoCurrency",
  "CurrencyGoogle",
  "Message" AS "Message_u0",
  "PaymentMethod",
  "PartPaymentCode",
  "NewsLetterSubscription",
  "ShippingProvider",
  "IpAddress",
  "CouponCode",
  "PaymentStatus" AS "PaymentStatus_u0",
  "RefundHistories",
  "Currency",
  "BoundToCustomer",
  "UseDiscountProduct",
  "__KEY_Results",
  "__FK_Results",

  (SELECT
   "Email",
   "Ssn",
   "CompanyName" AS "CompanyName_u1",
   "FirstName" AS "FirstName_u1",
   "LastName" AS "LastName_u1",
   "FullName" AS "FullName_u1",
   "CellPhone" AS "CellPhone_u1",
   "VisitorId",
   "BirthDay",
   "IsCompany",
   "AuditInfo",
   "Gender",
   "LobCustomerNumber",
   "LastRemarketEvent",
   "__KEY_Customer",
   "__FK_Customer",
   (SELECT
    "Id" AS "Id_u2",
    "FullName",
    "FirstName",
    "LastName",
    "AddressLine1",
    "AddressLine2",
    "ZipCode",
    "City",
    "Country",
    "Phone",
    "CellPhone",
    "CompanyName",
    "Reference",
    "AddressId",
    "__FK_BillingAddress"
   FROM "BillingAddress" FK "__FK_BillingAddress"),
   (SELECT
    "Id" AS "Id_u3",
    "FullName" AS "FullName_u0",
    "FirstName" AS "FirstName_u0",
    "LastName" AS "LastName_u0",
    "AddressLine1" AS "AddressLine1_u0",
    "AddressLine2" AS "AddressLine2_u0",
    "ZipCode" AS "ZipCode_u0",
    "City" AS "City_u0",
    "Country" AS "Country_u0",
    "Phone" AS "Phone_u0",
    "CellPhone" AS "CellPhone_u0",
    "CompanyName" AS "CompanyName_u0",
    "Reference" AS "Reference_u0",
    "AddressId" AS "AddressId_u0",
    "__FK_ShippingAddress"
   FROM "ShippingAddress" FK "__FK_ShippingAddress")
  FROM "Customer" PK "__KEY_Customer" FK "__FK_Customer"),

  (SELECT
   "Raven-Entity-Name",
   "Raven-Clr-Type",
   "Non-Authoritative-Information",
   "@id",
   "Temp-Index-Score",
   "Last-Modified",
   "Raven-Last-Modified",
   "@etag",
   "SerializedSizeOnDisk",
   "__FK_@metadata"
  FROM "@metadata" FK "__FK_@metadata")
FROM "Results" PK "__KEY_Results" FK "__FK_Results")
FROM JSON (wrap on) "root" PK "__KEY_root";

4 Replies
Partner
Partner

Re: Where statement in Qlik Rest Connector , Incremental Load

Hi Lars,

any updates over this query of yours.
Have  you figured out this?

Partner
Partner

Re: Where statement in Qlik Rest Connector , Incremental Load

Hi Shweta

Actually Not.

Rgrds   Lars

rai-jmcpheeters
New Contributor III

Re: Where statement in Qlik Rest Connector , Incremental Load

Hey Lars,

Typically for this you have to pass the "where clause" into the connection rather than into the SQL select query. I've not worked with Raven DB, but there is documentation about how to query the database to get the desired information.

Most of the time what you need to do is add "WITH CONNECTION()" after your last FROM statement where you can pass information to the database. I have a simple example below - it won't be exactly what you need for Raven DB, but hopefully it can give you a nudge in the right direction.

SQL SELECT

     "id" as "id",

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

     WITH CONNECTION(

          QUERY "date" ">=1/1/2018");

Learning the API intricacies of various tools can take quite a long time with a TON of trial and error before figuring out what works, especially when no one has posted anything about that specific tool before.

pawwy1415
Contributor II

Re: Where statement in Qlik Rest Connector , Incremental Load

Hi @rai-jmcpheeters ,

I am looking for the solution to filter the data from rest connector.

I have a query like below

SQL SELECT

     "id" as "id",

    "name"  as "name_u1"

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

 

From name field I have to filter only Approved Data i.e., name= 'Approved'.

Could you please help me to write the condition on API. I have tried query Parameters but no luck.

Thanks in advance.