Name column derived from SharePoint displays number as its values

[apoorvasd]

Hello Everyone,

I am pulling data from SharePoint list into QlikView, where this list is first exported to MS access and then QlikView pulls data from it.

I am facing an issue w.r.t. "Name" field, where the names are displayed in number format in QlikView which are actually text values.

For example, if column, "Modified By" in SharePoint has A, B, C as its values, when I call this field in to QlikView "Modified By" has 1, 2, 3 as its values. But I would want these to be displayed in text format in QlikView too.

Any help on this?

Thank you.

[avkeep01]

Hi Apoorva Dhulehole,

My guess would be that the values in SharePoint are actually numbers but mapped with the corresponding texts. You probably need a translation table to convert the numbers to the correct text values.

[apoorvasd]

Hi Anton,

Thanks for your response.

These values in SharePoint come under the format "People and Group". These values are directly pulled from Active directory. Hence, I guess these would not be in number format.

Could you please elaborate on your point on having a translation table?

Thank you.

[avkeep01]

Hi,

You can make it in the script in your Qlik app with an InlineTable on top of that you can use a MAPPING LOAD statement which loads the data into the memory, but not as a visible table. When you load the data from Access you can apply this table.

TranslateTable:

MAPPING LOAD * INLINE [

FieldValue, Description

1, A

2, B

3, C

];

//When loading from access;

TableWithDataFromAccess:

LOAD

APPLYMAP('TranslateTable',[Modified By]) AS [Modified By Description],

"Modified By",

Field1,

Fied2,

Etc

FROM AccessDatabase.aacdb;

[apoorvasd]

But, every time there is a new entry in this field I will have to add this to my inline table, which I would no prefer!

Is there any other way to handle this?

[avkeep01]

Is there a table where the field values and numbers are filled? I mean the "modified by" description is probably a user or employees table or something in that direction.

Then you can replace the inline part with select fields from table

[apoorvasd]

No there is no separate table where these values are filled. They are done in SharePoint.

[avkeep01]

Alright, if there is no source for the description for Modified by then you can export an Excel file and use the excel file for the mapping table.

Another thing: so if the description exists in SharePoint, why doesn't it exists in Access? I mean in Access pulls the data from SharePoint and Qlik pulls the data from Access. So why don't add the description field in Access?

[apoorvasd]

Yes, in SharePoint I see name as text, but when exported to MS access I don't see the field at all (may be it is hidden). But, when I pull data from Access db in to, this field displays numbers instead of text values in it.

[avkeep01]

That is why I suspect that SharePoints maps the numbers with the correct text. So you see text, but it is actually text mapped on to a number. So in the export you'll find the numbers because the table doesn't have the text. The text is stored somewhere else.

Best solution:

Export the table with ID and Description, and add to Qlik

Second best solution:

Manually export ID and Description to Excel and update when new ID's appear or when ID's/Descrption changes

Third (not so great) solution:

Manually input all the ID's and descpriptions. Also maintain the list for every single new or changed ID.