Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Qlik Sense - Cleaning up data retrieved from REST Connector

Good afternoon,

I am new to Qlik Sense so apologies if my post is a bit sloppy/is missing required elements. I need assistance in trying to find the best method for cleaning up data brought into our Qlik Sense environment via a REST Connector. Through the GET request in the data load editor, we're retrieving a search results list that looks like this in the vendor's system (only has as few thousand rows overall):

Col ACol B Col CCol DCol E
Title 1123Person 1
Title 2123Person 2
Title 2123Person 3
Title 3246Person 1

 

The Qlik Sense REST Connector creates a generic RestConnectorMasterTable that looks like this:

RestConnectorMasterTable:

SQL Select

"total",

"__KEY_root",

(SELECT

     "Col A",

     "_KEY_records",

    "_FK_ColA"

...

These SELECT statements continue for each column and includes a default _KEY_record corresponding to their row number in the vendor's system, which serves as a unique ID for each row so to speak. The result is several tables for each column/row number split off from the initial RestConnectorMasterTable. 

I need to recreate the vendor table, which has a one to many structure so ApplyMap doesn't work beyond the first record match and Concatenate simply rebuilds the RestConnectorMasterTable  with each column's values and cells with null values for the other columns. I tried using a LEFT JOIN to build a table, but got a huge number of results that ended up stalling the system during a data load. I had some success using Lookup(), but that only works for specific rows.  

If this makes sense, what is an effective approach for cleaning up the retrieved data (e.g. make it look like the table from the vendor's system within Qlik)? Ideally, I'd like to implement something like ApplyMap(), but would loop through rows to fill in all matches based on the _KEY_record value.

Thanks!

Labels (3)
0 Replies