Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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