Re: Automatic update date in REST connector - Qlik Community

jood_ahmad · ‎2019-06-19

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

justinvchiang · ‎2019-06-19

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!

jood_ahmad · ‎2019-06-20

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?

justinvchiang · ‎2019-06-20

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/'

jood_ahmad · ‎2019-06-22

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;

jood_ahmad · ‎2019-06-23

@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;------...;

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.

justinvchiang · ‎2019-06-23

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

jood_ahmad · ‎2019-06-28

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;

justinvchiang · ‎2019-06-29

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)'

;

jood_ahmad · ‎2019-06-30

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.

Automatic update date in REST connector

REST Connector