topic Re: Replace If Condition in QlikView

Replace If Condition

dseelam — Thu, 17 Nov 2016 20:11:22 GMT

Hello All,

Is there a way I can replace if condition below and create new field

Load *,1 as BBB,

IF ([Channels]='CLUB','CLUB',

IF([Channels]='ONLINE','ONLINE',

IF([Channels] ='DEPT. STORE','DEPT. STORE',

IF ([Channels] = 'SPECIALITY STORES', 'SPECIALITY STORES',

IF ([Channels] = 'MASS MERCHANT', 'MASS MERCHANT',

IF ([Channels] = 'DISCOUNTERS', 'DISCOUNTERS',

IF ([Channels] = 'HOSPITALITY', 'HOSPITALITY',

IF ([Channel] = 'Discounters', 'DISCOUNTERS',

IF ([Channel] = 'Mass Merchant', 'MASS MERCHANT',

IF ([Channel] = 'Hospitality', 'HOSPITALITY',

IF ([Channel] = 'Speciality Stores', 'SPECIALITY STORES',

IF ([Channel] = 'Dept. Store', 'DEPT. STORE',

IF ([Channel] = 'Online', 'ONLINE',

IF ([Channel] = 'Club', 'CLUB',

))))))))))))))as [New Channels]

Resident FG;

It is making my query very slow, It would be great if some one suggests me alternative

Re: Replace If Condition

maxgro — Thu, 17 Nov 2016 20:32:51 GMT

maybe

upper(Channel) as [New Channels]

Re: Replace If Condition

dseelam — Thu, 17 Nov 2016 21:03:27 GMT

Massimo,

I am asking is there any other fictions I can use instead of if condition above

Re: Replace If Condition

Clever_Anjos — Thu, 17 Nov 2016 21:11:49 GMT

Use mapping functions: ApplyMap ‒ QlikView

Re: Replace If Condition

vishsaggi — Thu, 17 Nov 2016 21:21:05 GMT

As Clever mentioned you can write ApplyMap like below: Try and let us know if it worked!

Table1:

MAPPING LOAD * INLINE [

[Channels], CaseName

'CLUB','CLUB'

'ONLINE','ONLINE'

'DEPT. STORE','DEPT. STORE'

'SPECIALITY STORES', 'SPECIALITY STORES'

'MASS MERCHANT', 'MASS MERCHANT'

'DISCOUNTERS', 'DISCOUNTERS'

'HOSPITALITY', 'HOSPITALITY'

'Discounters', 'DISCOUNTERS'

'Mass Merchant', 'MASS MERCHANT'

'Hospitality', 'HOSPITALITY'

'Speciality Stores', 'SPECIALITY STORES'

'Dept. Store', 'DEPT. STORE'

'Online', 'ONLINE'

'Club', 'CLUB'

];

Load *,1 as BBB,

APPLYMAP('Table1', [Channels]) as [New Channels]

Resident FG;

Re: Replace If Condition

dseelam — Thu, 17 Nov 2016 21:44:54 GMT

HI Vishwanath,

The whole purpose of If condition is there are 2 fields channel and channels in table FG so want to create new channels field depending on 2 fields (channel & Channels)

If you look at if condition I want to give priority to channels field then take channel field in to considerations

Eg; Account numbers that have blank channels are feeding out through with excel as channel

Re: Replace If Condition

maxgro — Thu, 17 Nov 2016 22:19:08 GMT

maybe a nested applymap

Map1:

MAPPING LOAD * INLINE [

f1, f2

'CLUB','CLUB'

'ONLINE','ONLINE'

'DEPT. STORE','DEPT. STORE'

'SPECIALITY STORES', 'SPECIALITY STORES'

'MASS MERCHANT', 'MASS MERCHANT'

'DISCOUNTERS', 'DISCOUNTERS'

'HOSPITALITY', 'HOSPITALITY'

];

Map2:

MAPPING LOAD * INLINE [

f1, f2

'Discounters', 'DISCOUNTERS'

'Mass Merchant', 'MASS MERCHANT'

'Hospitality', 'HOSPITALITY'

'Speciality Stores', 'SPECIALITY STORES'

'Dept. Store', 'DEPT. STORE'

'Online', 'ONLINE'

'Club', 'CLUB'

];

load

ApplyMap('Map1', Channels, ApplyMap('Map2', Channel, null())) as NewChannels

inline [

Channels, Channel

'ONLINE', 'Online'

'DISCOUNTERS', 'aaaa'

'Discounters', 'bbbb'

'cccccc', 'Discounters'

];

Re: Replace If Condition

johnw — Thu, 17 Nov 2016 22:37:07 GMT

Or just the first map, I'd think?

applymap('Map1',Channels,applymap('Map1',upper(Channel),null())) as [New Channels]

And since in that map the from and to are the same:

Map1:
MAPPING LOAD *
,From as To
INLINE [
From
CLUB
ONLINE
DEPT. STORE
SPECIALTY STORES
MASS MERCHANT
DISCOUNTERS
HOSPITALITY
];