topic Re: consolidating values into one value in QlikView

consolidating values into one value

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have created a Performance chart for my team.

data stewards log their performance with project name, number of lines reviewed.

but the problem is that they all have named the project name slightly different.

ex: Data Cleanup , Data Clean Up, Project - Data Clean up, Cleaning up Date, Project Name : Data Clean up... etc...

So in the future I will provide dropdown so that they can choose the name of the project.

but the past data are all pretty messy as you can see from the example that I provided above.

all those values are under the column named "Project Name"

is there any way that i can write up something so that QV can consolidate those names into one single name for example "Data Cleanup"

there are other projects too so in order to compare them properly I need these to fall under same bucket.

Thank you!!

Mon, 25 Jul 2016 17:43:30 GMT

you can rename your fields wicth "as"

load

Cleanup Data as [Cleanup Data],

....

from ...table a;

load

Data Cleanup as [Cleanup Data],

....

from ...table b;

Clever_Anjos — Mon, 25 Jul 2016 17:45:18 GMT

You can use a Mapping table to rename your projects

Map:

Mapping LOAD * inline [

Old, New

Data Cleanup , Project - Data Clean up

Data Clean Up, Project - Data Clean up

Project - Data Clean up, Project - Data Clean up

Cleaning up Date, Project - Data Clean up

Project Name : Data Clean up, Project - Data Clean up

];

Map ProjectName using Map;

alexpanjhc — Mon, 25 Jul 2016 18:05:53 GMT

load *,

if(wildmatch(projectname, '*Clean*' )>0, 'Project - Data Clean Up','') as projectname

from table

Mon, 25 Jul 2016 18:53:28 GMT

Thank you alex! this worked perfectly