clean dimension values or categorise them

[surajap123]

Hi All,

I want to group field(dimension) values into a different dimension, ie My current field values are are not clean, so i am trying for the best way to categorize it or atleast trim the values like below example.

Field Value Example-

from-

CASH DCN2 JUL02....

CASH DCN3 JUL05...

to-

CASH

Please help me how to achieve this.

Thanks!!

[lironbaram]

hi

assuming you want only the first word in field

you can use

subfield(YOUR_FIELD ,' ',1)  which will give you the first word in each value

[#3]

using left() can achieve this:.

Left('abcdef', 3)

Returns 'abc'

FYR: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/StringFunctions/Left.h...

[surajap123]

Thanks for all the replies!!

My values are like below-

CASH BNKM    NOV15K&H

CASH DCN2    AUG083140

BOOTS PH

TRADERS Ltd

The first and second values are actually same. So I want to make them as 'CASH'

The 3rd and 4th values are completely different, so i don't want to trim them.

How to achieve this.

[#5]

Liron's suggestion will work in your case, you can have a try!

[varshavig12]

What is the expected output?

Like below ?

CASH

CASH

BOOTS

TRADERS

if not can you please also post that.

[arulsettu]

if yes try this in list boc expression    

if(WildMatch(fieldname,'*CASH*'),'CASH',fieldname)

[surajap123]

Thanks for all the inputs. They give me ideas.

Infact i have many values in the are duplicates,unique, special characters etc., so I think with a single calculated field i cannot achieve it completely.

TransactionType

CASH BNKM    NOV15K&H

CASH DCN2    AUG083140

BOOTS PH

TRADERS Ltd

******************************

TRADERS Ltd*************

1.If you see below, few values are like complete star line(*******) and few (TRADERS Ltd****) with special characters, that i want to clean.

2.Also want to merge few values like(CASH*), replace star lines with a value in different field in the table.

Is it possible to achieve this using lookup table like Mapping?

[arulsettu]

post sample file to try

[varshavig12]

Like this ?

n if not can you please post the expected output ?