Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
maybe
upper(Channel) as [New Channels]
Massimo,
I am asking is there any other fictions I can use instead of if condition above
Use mapping functions: ApplyMap ‒ QlikView
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;
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
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'
];
Z:
load
*,
ApplyMap('Map1', Channels, ApplyMap('Map2', Channel, null())) as NewChannels
inline [
Channels, Channel
'ONLINE', 'Online'
'DISCOUNTERS', 'aaaa'
'Discounters', 'bbbb'
'cccccc', 'Discounters'
];
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
];