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: 
arixooo123
Creator III
Creator III

Rename Data when Reading(Loading) from SQL Server

Hello Guys

I have a table which i am loading it from SQL Server 2008,

I want to rename the data in a way which is understandable to Users,

for Example:

I have 12 columns which each one has 5 possible values (1 or 4 or 9 or 16 or 25), i want to represent these data as (Highly disagree, disagree, no opinion, agree, highly agree)

take a look at this image:

Comparative.png

as you see in the above picture, many users wont understand the meaning of these numbers!.

thanks

1 Solution

Accepted Solutions
maxgro
MVP
MVP

in chart

if GroupA is your dimension in bar chart you can replace with a calculated dimension

=pick(match(GroupA,1,4,9,16,25), 'Highly disagree', 'disagree', 'no opinion', 'agree', 'highly agree')

or if you prefer you can do the same thing in the script

// make some test data with 1, 2, 9, 16, 25

sqlserver:

Load

  rowno() as id,

Pick(Ceil(5*Rand1),1,4,9,16,25) as GroupA;

Load

Rand() as Rand1

Autogenerate 1000

While Rand()<=0.5 or IterNo()=1;

// replace 1,4,9.... with Highly......

final:

load

  id,

  pick(match(GroupA,1,4,9,16,25), 'Highly disagree', 'disagree', 'no opinion', 'agree', 'highly agree') as NewGroupA,

  GroupA

Resident

  sqlserver;

drop table sqlserver;

View solution in original post

4 Replies
qlikoqlik
Creator
Creator

Hi Arman

Use cross table and combine all those 11 columns into one and then you can easily rename the values to whatever you like

thanks and regards

Padma

maxgro
MVP
MVP

in chart

if GroupA is your dimension in bar chart you can replace with a calculated dimension

=pick(match(GroupA,1,4,9,16,25), 'Highly disagree', 'disagree', 'no opinion', 'agree', 'highly agree')

or if you prefer you can do the same thing in the script

// make some test data with 1, 2, 9, 16, 25

sqlserver:

Load

  rowno() as id,

Pick(Ceil(5*Rand1),1,4,9,16,25) as GroupA;

Load

Rand() as Rand1

Autogenerate 1000

While Rand()<=0.5 or IterNo()=1;

// replace 1,4,9.... with Highly......

final:

load

  id,

  pick(match(GroupA,1,4,9,16,25), 'Highly disagree', 'disagree', 'no opinion', 'agree', 'highly agree') as NewGroupA,

  GroupA

Resident

  sqlserver;

drop table sqlserver;

MarcoWedel

- create a mapping table

- use applymap during load of your data

- maybe combine with the dual function, so your original numerical values are still available for sorting purposes.

regards

Marco

arixooo123
Creator III
Creator III
Author

Thanks Buddy, that works like a charm.

i like to see the dimension in a order i want. how to sort them? i know i can go to the sort tab and i should write an Expression beside Expression check box.

but what Expression?

Something like :

SortInThisWay('Highly Disagree','Disagree','No Opinion','Agree','Highly Agree')

I need to tell you that i created a cyclic group including 12 Expressions one for each column.

the name of this Cyclic group is QST, i tried this for sorting:

Match(QST,'Highly Disagree','Disagree','No Opinion','Agree','Highly Agree')

but it says bad field name : QST because QST is not actually a field