I have a working rest connection against a Azure Cosmos DB API. What I am trying to do is include a where clause on the "_ts" field (time stamp in a unix format). The reason for doing this is so I don't reload the entire API contents every time and set up weekly incremental loads. Later on in the UI dashboard I convert this to a normal date format using date(floor([_ts]/86400)+25569,'YYYY-MM-DD') as Date. My issue is I can not seem to figure out where to put it and with what syntax. Any help would be appreciated.
Week_Period_List: load date(weekstart(Today()),'YYYY-MM-DD') as Week_Period AutoGenerate(1) ;
concatenate(Week_Period_List)
load date(weekstart((Today()-6)),'YYYY-MM-DD') as Week_Period AutoGenerate(1) ;
FOR v_RowCounter = 0 TO NoOfRows('Week_Period_List')-1;
LET v_WeekPeriod = peek('Week_Period',$(v_RowCounter),'Week_Period_List');
LIB CONNECT TO 'REST_CosmosDB_Data_Retrieve';
RestConnectorMasterTable: SQL SELECT "_rid" AS "_rid_u0", "__KEY_root", (SELECT "id", "RequestPath", "GatewayType", "SourceTransactionId", "ChannelId", "AppId", "Status", "IsSuccess", "TransactionAmount", "ResponseCode", "Duration", "_rid", "_self", "_etag", "_attachments", "_ts", <-- Unix time stamp formatted like this 1670112040 is really 2022-12-04. I want to pass the v_WeekPeriod variable in a where clause "PolicyNumber", "BillingAccountNumber", "ErrorMessages", "Exception", "__KEY_Documents", "__FK_Documents", (SELECT "ProcessorResponseCode", "ProcessorResponseText", "TransactionId", "__FK_TransactionLog" FROM "TransactionLog" FK "__FK_TransactionLog") FROM "Documents" PK "__KEY_Documents" FK "__FK_Documents") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(url "$(v_EnvironmentURL)", HTTPHEADER "Authorization" "$(vSessionId)", HTTPHEADER "x-ms-date" "$(vMS_Date)", HTTPHEADER "x-ms-version" "$(vMS_Version)", HTTPHEADER "x-ms-max-item-count" "10" ) ;
