need help in if condition/case statement. ?

[vishalgoud]

we have data from 2015 jan to 2017 dec , from sep 2017 new filter added called Host_type which is having 3 values called BB-HOSTED,MM-CONTROLLED,-1

for the data from 2015 Jan to Aug 2017, no filter so we are taking HOST_TYPE filter with a defualt value called : UNKNOWN.

now i want to replace -1 with UNK the below expression is not working - Please help UNK missing in the final filter.

if(HOST_TYPE = 'BB-HOSTED','BB-HOSTED',
if(HOST_TYPE = 'UNKNOWN','UNKNOWN',
if(HOST_TYPE = 'MM-CONTROLLED','MM-CONTROLLED',
if(HOST_TYPE = '-1','UNK')))) AS HOST_TYPE,

doing this chnage at extractor level is good or in the final application is good ??

Thanks in advance.

[sunny_talwar]

Does it actually contain -1 as value? or is it something else...

To simplify, you can try this

If(HOST_TYPE = -1, 'UNK', HOST_TYPE) as HOST_TYPE

[sunny_talwar]

Or this

If(Match(HOST_TYPE, 'BB-HOSTED', 'UNKNOWN', 'MM-CONTROLLED'), HOST_TYPE, 'UNK') as HOST_TYPE

[vishalgoud]

yes Sunny  it contain -1 as a value, simply in the host type list box we can see 3 values  'BB-HOSTED', 'MM-CONTROLLED' and -1.

before aug 2017 qvds host type contains only one values that is 'UNKNOWN'

in list box i used pick and macth where it is working fine but in the selection box still we can see that -1 when we select UNK.

so to change it completely am thinking to do it in script level. where am trying to use the above condition which is not working .

Thanks for your response.

[sunny_talwar]

What was your pick match statement for the list box?

[vishalgoud]

=Pick(Match(HOST_TYPE,'UNKNOWN','MM-CONTROLLED','BB-HOSTED','-1'),'UNKNOWN','MM-CONTROLLED','BB-HOSTED','UNK')

[sunny_talwar · Accepted solution]

Can you try the same in the script (If it worked on the front end, I see no reason for it to not work in the script)

Pick(Match(HOST_TYPE,'UNKNOWN','MM-CONTROLLED','BB-HOSTED','-1'),'UNKNOWN','MM-CONTROLLED','BB-HOSTED','UNK') as HOST_TYPE_NEW

I renamed the field to HOST_TYPE_NEW

Now create a table box and add HOST_TYPE and HOST_TYPE_NEW to it and see if -1 is now UNK within HOST_TYPE_NEW