Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
oykyes
Contributor II
Contributor II

writing 2 different variables as 1

Hi guys

I have a very simple question. There is a product table in my database and in that  table I have a 'Category' column .In that column there are  variables like  watch,jewellery,accesories etc. But table is not that clean as it should be. For example just for accesories, there are more than one name. Lower case, upper case... different ways to dedicate 1 category. I want to take all of these types as 1 and put them in my chart. For example for accesories I have 'accesories' and 'ACCESORIES' when I write it as if (lower(category)='accesories',category). It takes both of the names separately. How ca I write it as 1 while I dont lose any data. 

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

The most general way to solve this is to use a mapping table to map the incorrect items to a common item. Something like this code sample:

Map_Cleanup:
Mapping LOAD * Inline
[
From, To
	WxAtCH,Watch
	Acessssories,Accesories
	Watches,Watch
];

Source:
LOAD ID,
	Proper(ApplyMap('Map_Cleanup', Product,Product)) as Product,
	Sales
Inline
[
	ID,Product,Sales
	1,ACCESORIES,100
	2,WATCH,120
	3,accesories,130
	4,Accesories,220
	5,WxAtCH,89
	6,Acessssories,100
	7,Watches,20
];

In this example, the ApplyMap corrects the 3 misspelled items and the Proper() converts them all to a common case (you could use Upper() or Lower() instead if you prefer).

I have used in-lines for illustration purposes, but the mappings could easily come from a file or database table.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jupitopino
Partner - Contributor III
Partner - Contributor III

Hi Oykes.

 

The best way to do this, is creating a previos flow/process  of datacleasing, either with the laod script or an ETL...

In your case, a quick way to solve your problem may be to use this function on the product field in the load script..:

load
Replace(Upper(ltrim(rtrim(Product))),' ','') ,
sales
FROM ..........

With this function, you can normalize diferent values for de producto field..:

Product;sales
ACCESORIES;100
WATCH;120
accesories;130
Accesories;220
W AtCH;89

 

Kind regards.

oykyes
Contributor II
Contributor II
Author

Thank you Jupitopino,

It worked while the difference is only upper case, lower case or space but when we have a different difference between 2 variables,I still can't merge them. The table that I mentioned also uses multiple languages for the same variable so I also need to merge them in 1 variable. Do you know how to do that??

best regards...

jonathandienst
Partner - Champion III
Partner - Champion III

The most general way to solve this is to use a mapping table to map the incorrect items to a common item. Something like this code sample:

Map_Cleanup:
Mapping LOAD * Inline
[
From, To
	WxAtCH,Watch
	Acessssories,Accesories
	Watches,Watch
];

Source:
LOAD ID,
	Proper(ApplyMap('Map_Cleanup', Product,Product)) as Product,
	Sales
Inline
[
	ID,Product,Sales
	1,ACCESORIES,100
	2,WATCH,120
	3,accesories,130
	4,Accesories,220
	5,WxAtCH,89
	6,Acessssories,100
	7,Watches,20
];

In this example, the ApplyMap corrects the 3 misspelled items and the Proper() converts them all to a common case (you could use Upper() or Lower() instead if you prefer).

I have used in-lines for illustration purposes, but the mappings could easily come from a file or database table.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
oykyes
Contributor II
Contributor II
Author

Thank you so much! It works really well...