topic Re: Two Values as one Value in QlikView

Two Values as one Value

Wed, 25 Nov 2020 16:16:04 GMT

I have this this values in a database .

Denied

Deny

Paid

Pay

Reverse

Rev

I want to convert Deny to Denied, Pay to Paid and Rev to Reverse so their information merge with their respective correct value. Any idea will be greatly appreciated.

Thanks

Re: Two Values as one Value

sunny_talwar — Wed, 18 May 2016 20:43:48 GMT

May be like this using a mapping load:

MappingTable:

Mapping

LOAD * Inline [

Name, NormalizedName

Deny, Denied

Pay, Paid

Rev, Reverse

];

Fact:

LOAD allOtherFields,

ApplyMap('MappingTable', FieldName) as FieldName

FROM Source;

Or you can do this using Match:

Pick(Match(FieldName, 'Deny', 'Pay', 'Rev') + 1, FieldName, 'Denied', 'Paid', 'Reverse') as FieldName

Re: Two Values as one Value

Anonymous — Wed, 18 May 2016 20:53:25 GMT

define a mapping table and use applymap

example:

TEST:
mapping
load * Inline [
In, Out
Deny, Denied
Pay, Paid
Rev, Reverse
];

ORIG:
Load * Inline [
Inp
Denied
Deny
Paid
Pay
Reverse
Rev
];

NEW:
Load Inp,
applymap('TEST', Inp) as Outp
resident ORIG;

]

Re: Two Values as one Value

swuehl — Wed, 18 May 2016 21:04:48 GMT

I would also suggest a MAPPING approach.

It's also explained here:

Data Cleansing

Re: Two Values as one Value

sunny_talwar — Wed, 18 May 2016 21:05:57 GMT

I tried to cover all my bases this time

Re: Two Values as one Value

sunny_talwar — Thu, 19 May 2016 12:56:41 GMT

Damn it!

I though I did cover all my bases, but guess not

Re: Two Values as one Value

Thu, 19 May 2016 12:59:06 GMT

But Really Helpful. Thanks!!!