topic data merge for rest api with variable using for loop in QlikView

data merge for rest api with variable using for loop

maohob1 — Sat, 16 Jul 2022 19:37:36 GMT

Hi , I Have similar this code ..

GeoData:
LOAD * INLINE [
sortID, LongVal, LatVal
100, 40.222, 34.221
200, 41.111, 34.333
300, 43.777, 35.555
400, 44.888, 34.111
500, 45.666, 34.998
];

let i=0;
let vRecord=NoOfRows('GeoData');

FOR i=0 to $(vRecord)

let VarLongitude = peek('GeoData.LongVal', $(i), 'GeoData');
let VarLatitude = peek('GeoData.LatVal', $(i) , 'GeoData');
let VsortID= peek('GeoData.sortID', $(i) , 'GeoData');

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=http://api.testname/arcgis/rest/services/MapServer/2/query?;
timeout=30;method=GET;queryParameters=geometry%2{x:$(VarLongitude),y:$(VarLatitude)}%1outFields%2City_ID";

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"__KEY_features",
"__FK_features",
(SELECT
"City_ID" AS "City_ID_",
"__FK_attributes"
FROM "attributes" FK "__FK_attributes")
FROM "features" PK "__KEY_features" FK "__FK_features")
FROM JSON (wrap on) "root" PK "__KEY_root";

[attributes]:
LOAD [City_ID]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attributes]);

Join

LOAD $(VarLongitude) as Longitude_N ,
$(VarLatitude) as Latitude_N ,
$(VsortID) as sortID_N
Resident GeoData ;

DROP Tables RestConnectorMasterTable;

EXIT SCRIPT when i=$(vRecord);
Next i;

i have INLINE table and use for loop to read data , that is ok .

But my problem is to associate the variables with a attributes table.

when reload Myapp.qvw

generate Syn Table , Because these tables are created ..

attributes-1

attributes-2

attributes-3

attributes-4

attributes-5

what i do to solve this problem !

Re: data merge for rest api with variable using for loop

maohob1 — Sun, 17 Jul 2022 06:09:46 GMT

To clarify what is required

i need final output like this..

City_ID	Longitude_N	Latitude_N	sortID_N
8	40.222	34.221	100
6	41.111	34.333	200
12	43.777	35.555	300
14	44.888	34.111	400
15	45.666	34.998	500

Re: data merge for rest api with variable using for loop

marksouzacosta — Sat, 15 Jun 2024 22:14:56 GMT

As an addition to this topic, I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa