Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
onmysi49
Contributor III
Contributor III

REST API data - Select All Fields

Hello,

I have a connection to one of core system's REST API set up. Everything including pagination is working fine for me. 

I am using WITH CONNECTION statement to adjust the URL in order to fetch data from different tables (entities) which REST API provides connection to.

Only issue here is, that it is really tricky for me to get all the field names which those entities include. My intention is to always fetch all fields from the table but SELECT * does not seem to work.

One workaround could be to obtain those field names from API using /describe method which API offers. This call returns field names which the particular table includes. But the issue here is that the result of this call in JSON looks e. g.:

"fields": {
"company_id": {
"type": "relation",
"required": true
},
"create_ts": {
"type": "datetime",
"required": false,
"default": "now"
},
"create_user": {
"type": "char",
"allow_blank": true,
"max_length": 30,
"required": false,
"default": ""
}

Which means that each field name is returned as table name (where the table itself includes field's properties like type etc.). And I am not able to get those chidlren table names returned as values so they could be used as field names in the following call to API.

Does anyone know about any workaround/solution?

I might have been missing something very simple as I am not so familiar with SQL.

Thanks a lot. Have a great day.

Labels (6)
14 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @110 

If you are using Sense client-managed or Desktop then you can just create a Web File connection (to any destination) and then use this code:

tmpCOVID:
CROSSTABLE (tmpDate, Cases, 4) LOAD
    *
FROM [lib://GenericWeb]
(URL IS "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv",
txt, codepage is 28591, embedded labels, delimiter is ',', msq);

COVID:
LOAD
    *,
    Year(Date) as Year,
    Month(Date) as [Month Name],
    Date(MonthStart(Date), 'MMM-YYYY') as Month
    ;
LOAD
    *,
    GeoMakePoint(Lat,Long) as MapPoint,
    Date(Date#(tmpDate, 'M/D/YY')) as Date
RESIDENT tmpCOVID;

DROP TABLE tmpCOVID;
DROP FIELD tmpDate;

 

Weirdly though, the Web File connector isn't available in Cloud.

If you are doing the same in Cloud you need to do this:

LIB CONNECT TO 'Data:GenericGET';

let sLoad = '';

for iDate = MakeDate(2020,1,22) to (today()-1)
	let sLoad = sLoad & ',
    	"' & date(iDate, 'M/D/YY') & '"';
next

tmpCOVID:
CROSSTABLE (tmpDate, Cases, 4) LOAD
    *
    ;
SQL SELECT 
    "Province/State",
    "Country/Region",
    "Lat",
    "Long"
    $(sLoad)
FROM CSV
(header on, delimiter ",", quote """") "CSV_source" 
WITH CONNECTION
(
URL "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv"
);

COVID:
LOAD
    *,
    Year(Date) as Year,
    Month(Date) as [Month Name],
    Date(MonthStart(Date), 'MMM-YYYY') as Month
    ;
LOAD
    *,
    GeoMakePoint(Lat,Long) as MapPoint,
    Date(Date#(tmpDate, 'M/D/YY')) as Date
RESIDENT tmpCOVID;

DROP TABLE tmpCOVID;
DROP FIELD tmpDate;

 

Again, the connection can be to any valid REST endpoint with a GET connection, as we overwrite that with the WITH CONNECTION. I tend to use https://jsonplaceholder.typicode.com/posts

That works at the moment as every single day between the 22nd Jan 2020 and yesterday is present. If that wasn't the case then it would be a bit trickier (you could do an IF in the FOR loop to avoid some known dates).

Another innovative approach that may work is this one from @IvanBozov who describes auto-updating data in a Google Sheet and then loading from that:
https://www.vizmind.eu/connecting-to-web-files-in-saas-editions-of-qlik-sense/

Hope that helps.

Out of interest, is there any double counting that needs to be dealt with in these data (where there is Province and State level data). If not I may use these as a demo feed for my Instant Sense App.

Cheers,

Steve

110
Contributor III
Contributor III

oh wow, thanks Steve -   That's really cool!   Yeah,  I used to have desktop, which I didn't think I'd miss until now. 

I've attached the  app +  the load script if it's of interest .

The data connections didn't save  - but I believe they were just references to web files  -  which as I'm using cloud isn't possible to re-use hence the conundrum. 🙂

For the double count / example  -  I don't think there's double counting, though I think in the load script I sum over country only and don't use the province data as half the time it wasn't consistent ( Some countries had it, some didn't ) 

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @110 

Nice. I hope that the ability to build out the load script to automate the REST load will mean you can have it automatically refresh.

It's a strange choice for them to give cumulative figures, but I see that you work that out in the script.

I think that I tried doing something over this source before, but stopped as the region data was tripping me up. I didn't get to the point of deciding just to ditch the sub-country numbers!

Cheers,
Steve

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi again @110 

Over on another thread (here@jptneumann has posted the excellent suggestion of using Qlik Application Automation to download the file. Once you have the CSV somewhere local you can just do the LOAD * from it to get all columns.

Cheers,

Steve

110
Contributor III
Contributor III

Wow alright, yeah that'd definitely save some work retooling load scripts etc.    I'll have a look  - thanks again Steve, this thread will be a goldmine for people searching this same thing in the future  ( as more people switch to qlik cloud and suddenly find their loads not working 😄