topic Name column derived from SharePoint displays number as its values in QlikView

Name column derived from SharePoint displays number as its values

apoorvasd — Thu, 30 Aug 2018 09:30:05 GMT

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.

Re: Name column derived from SharePoint displays number as its values

avkeep01 — Thu, 30 Aug 2018 09:34:47 GMT

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.

Re: Name column derived from SharePoint displays number as its values

apoorvasd — Thu, 30 Aug 2018 09:41:50 GMT

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.

Re: Name column derived from SharePoint displays number as its values

avkeep01 — Thu, 30 Aug 2018 10:14:48 GMT

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;

Re: Name column derived from SharePoint displays number as its values

apoorvasd — Thu, 30 Aug 2018 10:20:46 GMT

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?

Re: Name column derived from SharePoint displays number as its values

avkeep01 — Thu, 30 Aug 2018 10:34:31 GMT

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

Re: Name column derived from SharePoint displays number as its values

apoorvasd — Thu, 30 Aug 2018 11:04:23 GMT

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

Re: Name column derived from SharePoint displays number as its values

avkeep01 — Thu, 30 Aug 2018 11:16:05 GMT

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?

Re: Name column derived from SharePoint displays number as its values

apoorvasd — Thu, 30 Aug 2018 12:04:14 GMT

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.

Re: Name column derived from SharePoint displays number as its values

avkeep01 — Thu, 30 Aug 2018 12:38:48 GMT

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.

Re: Name column derived from SharePoint displays number as its values

apoorvasd — Wed, 05 Sep 2018 05:47:30 GMT

Hello,

Thank you so much for all your inputs!

May be I will try to ask the SharePoint team to provide one more column (only for QlikView) which would have the names in text format and see if this works!

Thank you.

Re: Name column derived from SharePoint displays number as its values

Amoon666 — Tue, 30 Nov 2021 22:21:17 GMT

Hi,

I currently face the same issue and would like to know how you resolved it.

Thank you

Re: Name column derived from SharePoint displays number as its values

David8733 — Fri, 03 Dec 2021 04:05:41 GMT

@apoorvasd wrote:

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. mymilestonecard

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.

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.