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: 
ianwickline
Contributor II
Contributor II

Key Generation Strategy - via REST API (SharePoint)

I have successfully established a new REST API connection to a SharePoint list.  99% of the data is coming back as expected.

Unfortunately one of my fields (perhaps the most important field in my data model) appears to be negatively impacted by the "Key Generation Strategy" option/feature that exists in the configuration of the REST API connection.  Reason that I say this is because the data that is present for this specific column appears inside Qlik Sense as either a unique sequence ID number (when selecting "sequence ID" or "no keys") or a generated hash code/value (when selecting "current record" or "fully qualified record").  I have tried all 4 options with the varying effects noted (but all 4 consistently manipulate this field to a value other than what I require/need). 

Given the data model that we have - this value needs to remain unchanged.  Can this be accomplished; if so, how?

Thanks in advance for your help/assistance.

Labels (3)
1 Solution

Accepted Solutions
ianwickline
Contributor II
Contributor II
Author

Hi all,

First things first - thanks for chiming in Tim; much appreciated!

Long story short the field I was trying to pull in had null values at the top of the XML feed which was causing the load script to not be generated correctly in order to capture the data that was not null in this column later in the XML feed.  I was able to sort the data coming back in the XML feed to ensure the populated fields were at the top of the XML feed.  This allowed the generated load script to be properly created ensuring these values are properly returned.

Here is the SharePoint URL I am using that made this possible:

https://<DOMAIN/PATH>/_api/Web/Lists('<LIST-ID>')/items?$top=5000&$orderby=CMDB_x002d_SysID%20desc

Thanks, Ian

View solution in original post

4 Replies
TimvB
Creator II
Creator II

I had a similar issue. I solved my problem with the function AutoNumber. I generated a unique integer key with the function and used that key in my data model. This however changes the original key from the REST API completely.

Maybe it helps.

ianwickline
Contributor II
Contributor II
Author

Hi Tim,

This didn't seem to solve the problem.  Here is what I changed in my load script:

Initial Script (Generated by QlikSense):

[CMDB_x002d_SysID]:
LOAD [null_u10],
[__FK_CMDB_x002d_SysID] AS [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_CMDB_x002d_SysID]);

Revised Script (per above recommendation/suggestion):

[CMDB_x002d_SysID]:
LOAD [null_u10],
[__FK_CMDB_x002d_SysID] AS [SysID],
AutoNumber([__FK_CMDB_x002d_SysID]) as [__KEY_properties]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_CMDB_x002d_SysID]);

Am I missing something here?  Sure do appreciate your willingness to help/assist.  Thank you!

Thanks, Ian

TimvB
Creator II
Creator II

Hi Ian,

I am afraid that the key generation strategy from the Qlik Sense REST API cannot ensure that the same records will always have the same key when you make a call. The "Current record" strategy might works best for you as it generates a hash key based on all the values of the current record and uses that as the primary key. It also generates a hash key based on all the values of the direct parent and uses that as the foreign key. However, once a value in a record changes the key will change as well...

What worked for me: I had a unique combination of fields that identified my fact records as well as some dimension tables. These unique fields never change. I created a table (keyTable) with my own key using AutoNumber(uniqueField1, uniqueField2). I stored this table with the unique key and the unique fields in a QVD. Every time the API gives new records that do not match with a unique key in my QVD, I generate a unique key and incremental load the new records to the keyTable. I used the keyTable to map the generated keys to other dimension tables that have the same set of unique fields.

Maybe this is a possible solution for you.

ianwickline
Contributor II
Contributor II
Author

Hi all,

First things first - thanks for chiming in Tim; much appreciated!

Long story short the field I was trying to pull in had null values at the top of the XML feed which was causing the load script to not be generated correctly in order to capture the data that was not null in this column later in the XML feed.  I was able to sort the data coming back in the XML feed to ensure the populated fields were at the top of the XML feed.  This allowed the generated load script to be properly created ensuring these values are properly returned.

Here is the SharePoint URL I am using that made this possible:

https://<DOMAIN/PATH>/_api/Web/Lists('<LIST-ID>')/items?$top=5000&$orderby=CMDB_x002d_SysID%20desc

Thanks, Ian