Where statement in Qlik Rest Connector , Incremental Load

[lsjogren]

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";

[shwetagupta]

Hi Lars,

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

[lsjogren]

Hi Shweta

Actually Not.

Rgrds   Lars

[Anonymous]

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]

Hi @Anonymous ,

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.