Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

lsjogren
New Contributor III

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

3 Replies
shwetagupta
Contributor

Re: Where statement in Qlik Rest Connector , Incremental Load

Hi Lars,

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

lsjogren
New Contributor III

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.

Community Browser