A native way of connecting to an on-premise Sharepoint list without having to add complexity to the script to process the resulting XML into the UI column names and removing data type prefixes from calculated columns.
I can connect ok using a Web File or REST connector, and it detects the Sharepoint list it as XML, but there are two issues:
1) The field names are the xml names so are prefixed ows_ etc. For example, Qlik sees a field called ows_Reference_x0020_Number which is really Reference_Number on the Sharepoint list. I get around this by reading in the xml/Schema/ElementType/AttributeType and using that as a mapping table to Rename Fields using FieldMap; That then leaves me with the 2nd issue.
2) Some field values are prefixed with string;# (i.e. the field type + ;#). In xml these show a field type variant and dt:lookup=true. Now we can manually remove these by adding Subfield into the load statement for each field of type variant but how could we do this programatically?
I believe all the fields requiring SubField are actually those that are calculated fields in Sharepoint. e.g. Sharepoint field CGATE-A-Future is calculated as "=[CGate-A Scheduled Date]-NOW()" and set to return a Number. In Qlik this comes through as, for example, float;#-44350.6619117245. Therefore every field affected has to have SubField manually added to the load script.
Ideally, the connector would recognise this as a Sharepoint list and handle the two points above automatically.
Further background. The URL used for the Web File or REST connector are derived by using the Sharepoint Export to Excel feature to generate the *.iqy file. Opening this .iqy file gives the URL include list and view UID as shown in the example below. If Excel is allowed to open this .iqy file then it deals with the two issues above automatically.
https://connect.int.gknaerospace.com/gknbusinesses/aerospace/functions/commercial/bidprocess/_vti_bi...
