I’ve been searching but haven’t been able to find in previous threads.
For an API i'm trying to extract to TX, i'm running into some issues.
The API extracts some geographical building data, based on an ID. I have these ID's from a seperate source already in the warehouse.
What I want to do is get the ID from the ODX-table and for each ID run a call to the API. I'm new to this scripting language and maybe im overlooking it in existing documentation but for me it seems impossible.
Any idea's and suggestions would be welcome, thanks!
Best answer by Thomas Lind
Here is what you can do. It is a bit back and forth I guess, but I can’t see any other ways currently.
You can mix JSON and CSV in a REST provider. So if you create the list of IDs in a CSV export and uses this as the source of a RSD file, you will then have it as a list in the normal REST data source. This can then be used as a normal table with a
SELECT * FROM REST.GEOLOCATION WHERE ID in (SELECT ID FROM REST.CSVFile)
It can be a bit complicated mixing csv and json rsd files, but it is possible, so if you get stuck I can give a few tips about setting it up.
You want geographical data. To get this you need to apply an id, this id can not come from a different data source if you want it to be available for a RSD file. You can set it manually but that is not very dynamic.
I don’t see how you could filter data either existing in another data source, or only in the ODX Store for any data source, both tables needs to exist in the source for it to be able to filter based on the other.
How do you get the ID though? It seems odd that the ID and the geographical info is not related at all.
In the original database there is information about buildings in the netherlands. This customer has a subset of data relevant to their location. (in example, I checked the cityID for one of those buildings in customer data and this shows 300k records, while their subset contains 12k of their region).
So yes, they are related. However. if I were to search for all the cities in this region I would get a million records and overload the API. Thats why I only want to get data for the buildings in my customers data. (Which are only 12k)
Here is what you can do. It is a bit back and forth I guess, but I can’t see any other ways currently.
You can mix JSON and CSV in a REST provider. So if you create the list of IDs in a CSV export and uses this as the source of a RSD file, you will then have it as a list in the normal REST data source. This can then be used as a normal table with a
SELECT * FROM REST.GEOLOCATION WHERE ID in (SELECT ID FROM REST.CSVFile)
It can be a bit complicated mixing csv and json rsd files, but it is possible, so if you get stuck I can give a few tips about setting it up.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
