topic Re: Rename values in a field in QlikView

Rename values in a field

Thu, 20 Mar 2014 16:26:00 GMT

I want to take some values in some fields and rename them to a more user friendly name.

For instance I made a quick table below for an example but my data is coming from SQL. I want to take the values only in the fields and rename them to what is in field2 column.

Field1	Field2
Name1	NewName
Name2	NewName2
Name3	NewName3
Name4	NewName4

I am making both a list box and a graph where these names need to be updated.

Thanks.

Re: Rename values in a field

MK_QSL — Thu, 20 Mar 2014 16:28:46 GMT

Can you elaborate your question and requirements with few line examples?

Re: Rename values in a field

its_anandrjs — Thu, 20 Mar 2014 16:33:42 GMT

Hi,

From your point of view its seems that you load a table and what ever field name is there you load that one and want to give good understandable name but keep both the name am i correct or not please confirm.

Ex:-

Fieldname NewName

name User_Name

Please explain more

Hope this helps

Thanks & Regards

Re: Rename values in a field

maxgro — Thu, 20 Mar 2014 16:34:31 GMT

=Pick(wildmatch(FIELDNAME,

'Name1', 'Name2', 'Name3', 'Name4', '*'),

'NewName1', 'NewName2', 'NewName3', 'NewName4', FIELDNAME)

Re: Rename values in a field

Thu, 20 Mar 2014 16:40:10 GMT

If there is a 1-1 relationship, you can display Field2 in your listbox or your graph.

If you really want to rename the content of field1, see MAPPING LOAD and APPLYMAP() function.

Fabrice

Re: Rename values in a field

Thu, 20 Mar 2014 16:41:06 GMT

create a mapping table

Maptest:

Mapping

Load * inline

[

Field1,	Field2
Name1,	NewName
Name2,	NewName2
Name3,	NewName3
Name4,	NewName4

];

use applymap function on your actual table while loading

ex: applymap('Maptest',Field1,'NA') as Fieldname

Re: Rename values in a field

its_anandrjs — Thu, 20 Mar 2014 16:52:34 GMT

Hi,

If you maintain Field name in different table so make another reference table and join this table with current table or simply leave this table with like condition with any key see below code.

If this is your requirement.

TablefromSQL:

LOAD RowNo() as rowid,Field1;

load * Inline

[

Field1

Name1

Name2

Name3

Name4

];

ReferenceTable:

LOAD RowNo() as rowid,Field2;

Load * inline

[

Field1, Field2

Name1, NewName

Name2, NewName2

Name3, NewName3

Name4, NewName4

];

Hope this helps

Thanks & Regards

Re: Rename values in a field

Thu, 20 Mar 2014 18:20:09 GMT

I think this is what I need but still having some trouble putting it all together this is what I have now but I am still getting load errors.

ServerMap:

Mapping

Load * inline

[

Old Field Name, New Field Name

Server1, USA 1

Server2, USA 2

Server3, Mexico

Server4, EMEA

];

ALLDEVICEINFO:

SQL SELECT

DISTINCT A0.DISPLAYNAME AS "Device Name", A0.TYPE AS "Type", A1.OSTYPE AS "OS Name", A0.INVENTORYSERVER AS "Inventory Server"

ApplyMap('ServerMap',OldFieldName,'NA') as Fieldname

FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn

WHERE (A0.DEVICENAME IS NOT NULL) ORDER BY A0.DISPLAYNAME

Re: Rename values in a field

Thu, 20 Mar 2014 18:24:02 GMT

applymap is a qlikview specific function. you will have use preceding load to get it work.

ex:

Load Field1,field2, applymap() as field3;

sql select * from table1;

Re: Rename values in a field

Thu, 20 Mar 2014 18:28:55 GMT

Something like:

ServerMap:

Mapping

Load * inline

[

Old Field Name, New Field Name

Server1, USA 1

Server2, USA 2

Server3, Mexico

Server4, EMEA

];

ApplyMap('ServerMap',OldFieldName,'NA') as Fieldname

ALLDEVICEINFO:

SQL SELECT

DISTINCT A0.DISPLAYNAME AS "Device Name", A0.TYPE AS "Type", A1.OSTYPE AS "OS Name", A0.INVENTORYSERVER AS "Inventory Server"

FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn

WHERE (A0.DEVICENAME IS NOT NULL) ORDER BY A0.DISPLAYNAME

Re: Re: Rename values in a field

pgrenier — Thu, 20 Mar 2014 18:43:56 GMT

Hello Mikel,

The ApplyMap() fonction should be called on a QlikView dataset and not on the source SQL.

ServerMap:

Mapping LOAD * Inline [

Old Field Name, New Field Name

Server1, USA 1

Server2, USA 2

Server3, Mexico

Server4, EMEA

];

ALLDEVICEINFO:

LOAD [Device Name], [OS Name], [Inventory Server]

, ApplyMap('ServerMap',OldFieldName,'NA') as Fieldname;

SQL SELECT DISTINCT A0.DISPLAYNAME AS "Device Name"

, A0.TYPE AS "Type", A1.OSTYPE AS "OS Name"

, A0.INVENTORYSERVER AS "Inventory Server"

, A0.SERVERNUMBER AS OldFieldName; // insert the proper source field name here

FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn

WHERE (A0.DEVICENAME IS NOT NULL) ORDER BY A0.DISPLAYNAME;

Regards,

Philippe

Re: Rename values in a field

Thu, 20 Mar 2014 18:44:29 GMT

load [Device Name], Type,[OS Name],[Inventory Server], ApplyMap('ServerMap',OldFieldName,'NA') as Fieldname;

SQL SELECT

DISTINCT A0.DISPLAYNAME AS "Device Name", A0.TYPE AS "Type", A1.OSTYPE AS "OS Name", A0.INVENTORYSERVER AS "Inventory Server"

FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn

WHERE (A0.DEVICENAME IS NOT NULL) ORDER BY A0.DISPLAYNAME;

Re: Re: Rename values in a field

Thu, 20 Mar 2014 19:27:48 GMT

I am working on it and getting there but the syntax is what gets me every time.

Re: Rename values in a field

Qrishna — Wed, 06 May 2015 17:49:52 GMT

Hi Mike,

Please see my post at

'Rename Field Values' and 'Select a Field Name from one list box to populate the field Values in another list box' , which may give you an idea on how to rename a field value.

Thanks

krishna