topic Re: Automatic update date in REST connector in Connectivity & Data Prep

Automatic update date in REST connector

jood_ahmad — Tue, 28 Dec 2021 19:35:31 GMT

Dears,

This is the scenario; I have done a connection to my API and I got the data but the method in fetching the data is I have to add in the URL the filters for the Date & the City day by day and city by city in separate connection and this is not practical.

so any way to have this in an automatic method to avoid these crazy things.

@robert_mika . @justinvchiang

Re: Automatic update date in REST connector

justinvchiang — Wed, 19 Jun 2019 23:10:11 GMT

Sounds like maybe a for Loop could solve this if you start with a list of the cities and days and then set them as variables as you iter through the load process.

Post a sample if you've got one and I'm sure others may jump in as well!

Re: Automatic update date in REST connector

jood_ahmad — Thu, 20 Jun 2019 11:09:18 GMT

Hi @justinvchiang ,

Thank you for the replay, actually,I have a question that is the for loop will apply on select from the url to the

RestConnectorMasterTable or on the whole load script.

can you share a sample if you have, please?

Re: Automatic update date in REST connector

justinvchiang — Thu, 20 Jun 2019 22:16:48 GMT

I'm thinking just a typical loop setting the dates and cities as variables that could be appended to your URL if your API URL were split up into parts as a string:

So typical loop

tmp_City:
LOAD City
FROM yoursourcetable;

LET vCities = NoOfRows ('tmp_City');

For j = 0 to $(vCities)-1

LET vCity = peek('City',$(j),'tmp_City');

            ApiLoad:
             LOAD x
             FROM URLPART & $(vCity) & URLOTHERPART
               ;
Next;

Where URLPART might be the 'https://api.whatever.com/apikey/other/'

and URLOTHERPART might be '/something_else/'

Re: Automatic update date in REST connector

jood_ahmad — Sat, 22 Jun 2019 18:47:41 GMT

her is a sample of what i'm trying to load because it gives an error when i use the for loop that need first to declare the two filrs : date & City, so how can we use that in for loop

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://xxxx.xxxxx.com/api/v2/orders?filters[date]%2$(2019-06-20)&filters[city]%2_XXXX; timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryHeaders=Authorization%2Bearer xxxxxxxxxxx xxxxxxx%1X-business%xxxxxxxxx;PaginationType=None;XUserId=xxxxxxx;XPassword=xxxxxxxx;";

RestConnectorMasterTable:
SQL SELECT
"guid" AS "guid_u1",
" AS "discount_amount_u0",
"__KEY_orders",
(SELECT
"@Value",
"__FK_kitchen_times"
FROM "kitchen_times" FK "__FK_kitchen_times" ArrayValueAlias "@Value"),
(SELECT
"guid",
"status",
"should_return_ingredients",
"quantity" AS "quantity_u0",
"__KEY_products",
"__FK_products",
(SELECT
"@Value" AS "@Value_u0",
"__FK_kitchen_times_u0"
FROM "kitchen_times" FK "__FK_kitchen_times_u0" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_removed_ingredients"
FROM "removed_ingredients" FK "__FK_removed_ingredients" ArrayValueAlias "@Value_u1"),
(SELECT
"hid",
"costingType",
"__KEY_options",
"__FK_options",
(SELECT
"quantity",
"cost",
"__FK_relationship_data"
FROM "relationship_data" FK "__FK_relationship_data")
FROM "options" PK "__KEY_options" FK "__FK_options")
FROM "products" PK "__KEY_products" FK "__FK_products"),
(SELECT
"hid" AS "hid_u1",
"__KEY_branch",
"__FK_branch",
(SELECT
"@Value" AS "@Value_u2",
"__FK_disabled_order_types"
FROM "disabled_order_types" FK "__FK_disabled_order_types" ArrayValueAlias "@Value_u2")
FROM "branch" PK "__KEY_branch" FK "__FK_branch"),
(SELECT
"hid" AS "hid_u2",
"pin",
"__FK_cashier"
FROM "cashier" FK "__FK_cashier"),
(SELECT
"hid" AS "hid_u3",
"__FK_device"
FROM "device" FK "__FK_device"),
(SELECT
"guid" AS "guid_u0",

"__KEY_payments",
"__FK_payments",
(SELECT
"hid" AS "hid_u4",
"__FK_payment_method"
FROM "payment_method" FK "__FK_payment_method"),
(SELECT
"hid" AS "hid_u5",
"pin" AS "pin_u0",
"__FK_employee"
FROM "employee" FK "__FK_employee")
FROM "payments" PK "__KEY_payments" FK "__FK_payments"),
(SELECT
"hid" AS "hid_u7",
"__KEY_taxes",
"__FK_taxes",
(SELECT
"amount" AS "amount_u0",
"percentage",
"__FK_relationship_data_u0"
FROM "relationship_data" FK "__FK_relationship_data_u0")
FROM "taxes" PK "__KEY_taxes" FK "__FK_taxes"),
(SELECT
"@Value" AS "@Value_u3",
"__FK_tags"
FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u3"),
(SELECT
"hid" AS "hid_u8",
"__FK_discount"
FROM "discount" FK "__FK_discount")
FROM JSON (wrap off) "orders" PK "__KEY_orders";

[orders]:
LOAD [guid_u1],
[reference],
[delivery_address],
[__KEY_orders],
1 as row_count#
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_orders]);

Join(orders)

[kitchen_times]:
LOAD [@Value],
[__FK_kitchen_times] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_kitchen_times]);

[options]:
LOAD [hid],
[costingType],
[__KEY_options],
[__FK_options] AS [__KEY_products]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_options]);

Join(options)

[relationship_data]:
LOAD [quantity],
[final_price],
[original_price],
[cost],
[__FK_relationship_data] AS [__KEY_options]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_relationship_data]);

[products]:
LOAD [guid],
[status],

[displayable_original_price],
[displayable_final_price],
[combo_option_size],
[__KEY_products],
[__FK_products] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_products]);

join(products)

[removed_ingredients]:
LOAD [@Value_u1],
[__FK_removed_ingredients] AS [__KEY_products]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_removed_ingredients]);

join(products)

[kitchen_times_u0]:
LOAD [@Value_u0],
[__FK_kitchen_times_u0] AS [__KEY_products]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_kitchen_times_u0]);

join(products)

Temp_options:
load *
Resident options;
DROP Table options;

Join(orders)
Temp_products:
LOAD *
Resident products;
DROP Table products;

[branch]:
LOAD [hid_u1],
[__KEY_branch],
[__FK_branch] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_branch]);

Join(branch)

[disabled_order_types]:
LOAD [@Value_u2],
[__FK_disabled_order_types] AS [__KEY_branch]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_disabled_order_types]);

Join(branch)

Temp_branch:
LOAD *
Resident branch;
DROP Table branch;

Join(orders)

[cashier]:
LOAD [hid_u2],
[pin],
[__FK_cashier] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_cashier]);

Join(orders)

[device]:
LOAD [hid_u3],
[__FK_device] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_device]);

[payments]:
LOAD [guid_u0],
[__KEY_payments],
[__FK_payments] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_payments]);

Join(payments)

[payment_method]:
LOAD [hid_u4],
[__FK_payment_method] AS [__KEY_payments]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_payment_method]);

Join(payments)

[employee]:
LOAD [hid_u5],
[pin_u0],
[__FK_employee] AS [__KEY_payments]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_employee]);

Join(orders)

Temp_payments_method:
Load *
Resident payments;
DROP Table payments;

[taxes]:
LOAD [hid_u7],
[__KEY_taxes],
[__FK_taxes] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_taxes]);

Join(taxes)

[relationship_data_u0]:
LOAD [amount_u0],
[percentage],
[__FK_relationship_data_u0] AS [__KEY_taxes]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_relationship_data_u0]);

Join(orders)

Temp_taxes:
load *
Resident taxes;
DROP Table taxes;

Join(orders)

[tags]:
LOAD [@Value_u3],
[__FK_tags] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

Join(orders)

[discount]:
LOAD [hid_u8],
[__FK_discount] AS [__KEY_orders]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_discount]);

DROP TABLE RestConnectorMasterTable;

Re: Automatic update date in REST connector

jood_ahmad — Sun, 23 Jun 2019 12:22:16 GMT

@justinvchiang I tried this loop only to get 1 day and i use the date as variable to loop on but the total row gives me duplicate I don't know why and the duplicate is that for loop is mixing between dates let us say that i need to load 2 days 2019-06-20 and 2019-06-19 all records for both dates are stored in both dates!

TRACE ----------Start Loop ----------;

let vToLoop=Num#(date('2019-06-20','YYYY-MM-DD') - date('2019-06-19','YYYY-MM-DD'));
TRACE ----- Loop number ----is $(vToLoop);
let j=0;
For j= 0 to $(vToLoop)
TRACE ------- Loop Value $(j);

let vCurrentDateLoop = Date(date('2019-06-20','YYYY-MM-DD') - vToLoop + j, 'YYYY-MM-DD');

TRACE ------ Current Date Loop $(vCurrentDateLoop);

CUSTOM CONNECT To "Provider=QvRestConnector.exe;url=https://www.-------.com/api/v2/orders?filters[date]%2$(vCurrentDateLoop)&filters[city]%2_55AD;---------ext;

RestConnectorMasterTable:

SQL select

FROM "discount" FK "__FK_discount")
FROM JSON (wrap off) "orders" PK "__KEY_orders";

Next j;

is that correct or their any bad loguc?

i want to loop on the date every day.

Re: Automatic update date in REST connector

justinvchiang — Sun, 23 Jun 2019 23:23:18 GMT

If it's giving you duplication it may be running twice even for one date. Maybe try

For j= 0 to $(vToLoop)-1

Another thought, maybe it would be easier to use the For Loop to generate a temp table of the various API URL permutations and then run your code in a loop against the list of URLs.

If you're doing this to generate something incremental, this would eventually just run against one date for all cities

Re: Automatic update date in REST connector

jood_ahmad — Fri, 28 Jun 2019 14:30:50 GMT

Hi @justinvchiang

sorry for the delay in the reply, i tried to have the loop over the URL and it is work but when i tried to apply the below for the QV load it gives me duplicate. if i load for 3 branches or 3 cities it duplicates the row over all the loop(3 times for the 3 branches or cites).

is it wrong what i'm doing or there is something missing?

TRACE >>>>>>>>>>>>>> Load list of Branches;
Branch_List:
LOAD [BranchName(en)],
hid_u1
FROM
[........list of branches test.xlsx]
(ooxml, embedded labels, table is Document_TB05);

Let vBranches_list=NoOfRows('Branch_List');

TRACE >>>>>>>>>>>> Number of Branches To Loop Over is $(vBranches_list);

TRACE >>>>>>>>>>>> Number of Branches To Loop Over is $(vBranches_list);

////-----------------------------------------------------------------------------------------
For vOrders_branch= 0 to $(vBranches_list) -1
Let vBranch_Hid= Peek('hid_u1',$(vOrders_branch),'Branch_List');

TRACE >>>>>>>>>>> Now Loading Branch $(vBranch_Hid);

let vOrdersToLoop=Num#(date('2019-06-23','YYYY-MM-DD') - date('2019-06-21','YYYY-MM-DD'));
TRACE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ----- Loop number ----is $(vOrdersToLoop);

For ord= 0 to $(vOrdersToLoop)-1
TRACE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ------- Loop Value $(ord);

let vCurrentOrdersDateLoop = Date(date('2019-06-23','YYYY-MM-DD') - vOrdersToLoop + ord, 'YYYY-MM-DD');

TRACE>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ------ Current Order Date Loop $(vCurrentOrdersDateLoop);

TRACE >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>--------------------- Load Order Table -----------------------;
[orders]:
LOAD
*
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_orders]);

NEXT ord;

Next vOrders_branch;

Re: Automatic update date in REST connector

justinvchiang — Sat, 29 Jun 2019 16:32:58 GMT

I think in your latest sample the problem may be that you're not using any of your loop variables in your LOAD statement so you're essentially getting all the things once for each loop.

You're going to want to set some sort of filter value per loop to be applied in the WHERE clause so as not to get duplication.

e.g.

[orders]:
LOAD
*
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_orders])

AND hid_u1 = '$(vBranch_Hid)'

;

Re: Automatic update date in REST connector

jood_ahmad — Sun, 30 Jun 2019 10:52:24 GMT

Hi @justinvchiang ,

yes you are right, but this is in case if i have the hid_u1 in Orders table so i can use the whare condition with it but i don't have that hid_u1 in that column because i'm get it in the same request with different table load.

Re: Automatic update date in REST connector

Gysbert_Wassenaar — Sun, 30 Jun 2019 14:23:42 GMT

Perhaps this document helps: https://community.qlik.com/t5/Qlik-Design-Blog/REST-connector-Using-WITH-CONNECTION/ba-p/1523257