topic Re: Where statement in Qlik Rest Connector , Incremental Load in Connectivity & Data Prep

Where statement in Qlik Rest Connector , Incremental Load

lsjogren — Fri, 02 Apr 2021 07:58:46 GMT

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

Re: Where statement in Qlik Rest Connector , Incremental Load

shwetagupta — Thu, 14 Sep 2017 09:28:56 GMT

Hi Lars,

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

Re: Where statement in Qlik Rest Connector , Incremental Load

lsjogren — Thu, 14 Sep 2017 09:31:48 GMT

Hi Shweta

Actually Not.

Rgrds Lars

Re: Where statement in Qlik Rest Connector , Incremental Load

Anonymous — Mon, 02 Jul 2018 16:19:46 GMT

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.

Re: Where statement in Qlik Rest Connector , Incremental Load

pawwy1415 — Tue, 03 Sep 2019 05:18:38 GMT

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.