topic Re: clean dimension values or categorise them in QlikView

clean dimension values or categorise them

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

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!!

Re: clean dimension values or categorise them

lironbaram — Tue, 12 Jul 2016 07:52:10 GMT

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

Re: clean dimension values or categorise them

Tue, 12 Jul 2016 07:53:10 GMT

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.htm

Re: clean dimension values or categorise them

surajap123 — Tue, 12 Jul 2016 08:05:24 GMT

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.

Re: clean dimension values or categorise them

Tue, 12 Jul 2016 08:12:19 GMT

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

Re: clean dimension values or categorise them

varshavig12 — Tue, 12 Jul 2016 08:13:12 GMT

What is the expected output?

Like below ?

CASH

BOOTS

TRADERS

if not can you please also post that.

Re: clean dimension values or categorise them

arulsettu — Tue, 12 Jul 2016 08:24:51 GMT

if yes try this in list boc expression

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

Re: clean dimension values or categorise them

surajap123 — Tue, 12 Jul 2016 08:48:52 GMT

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?

Re: clean dimension values or categorise them

arulsettu — Tue, 12 Jul 2016 08:54:52 GMT

post sample file to try

Re: clean dimension values or categorise them

varshavig12 — Tue, 12 Jul 2016 08:59:39 GMT

Like this ?

n if not can you please post the expected output ?

Re: clean dimension values or categorise them

surajap123 — Tue, 12 Jul 2016 09:55:33 GMT

Hi Varsha,

Yes, I am expecting similar result. Could you show the full expression.

Re: clean dimension values or categorise them

arulsettu — Tue, 12 Jul 2016 10:01:48 GMT

try this

=if(WildMatch(PurgeChar(fieldname,'0123456789*'),'*CASH*'),'CASH',PurgeChar(fieldname,'0123456789*'))

Re: clean dimension values or categorise them

varshavig12 — Tue, 12 Jul 2016 10:03:54 GMT

if(WildMatch(TransactionType,'*CASH*'),'CASH',trim(replace(TransactionType,'*','')))

Re: clean dimension values or categorise them

surajap123 — Tue, 12 Jul 2016 10:22:55 GMT

Thanks

I will come back after working.