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 A | Col B | Col C | Col D | Col E |
| Title 1 | 1 | 2 | 3 | Person 1 |
| Title 2 | 1 | 2 | 3 | Person 2 |
| Title 2 | 1 | 2 | 3 | Person 3 |
| Title 3 | 2 | 4 | 6 | Person 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!
