9 Replies Latest reply: Oct 4, 2017 1:32 PM by Joshua Russin RSS

    CASE statement in QlikView

      Hi,

       

      I was wondering if anyone would be able to help me.  I want to create the following Case statement in QlikView:

       

      CASE WHEN [Account Name] IN ('Hong Kong') THEN 'HK'

      WHEN [Account Name] IN ('USA') THEN 'NY'

      WHEN [Account Name] IN ('UK') THEN 'LDN'

      ELSE 'OTHER'

      END

       

      Is this possible to do within QlikView?

       

      Any help would be greatly appreciated.

       

      Many thanks.

       

      Rajiv.

        • Re: CASE statement in QlikView

          if(wildmatch([Account Name],'*Hong Kong*', 'HK',

          if(wildmatch([Account Name],'*USA*', 'NY',

          if(wildmatch([Account Name],'*UK*','LDN',

          'OTHER')))

           

           

          Wildmatch works if you need any string with the resulting '*string*' in it. If you are looking for exact match then just match(field,'String') will work too

            • CASE statement in QlikView

              That worked! Thank you!

              • Re: CASE statement in QlikView

                Mark,can u please help me with the below case statement .

                 

                CASE WHEN ITEM_ID LIKE '%CB%' THEN SUBSTR(ACCOUNT_NO,4,10)            

                         WHEN ITEM_ID LIKE 'D%'   THEN SUBSTR(ACCOUNT_NO,5,8)

                ELSE ACCOUNT_NO END AS InSite_Acct,

                 

                 

                I am trying like this below.It is giving me error.Can anybody tell me what am I doing wrong here?

                 

                if(WildMatch([ITEM ID],'*CB*',mid([Account Number],4,10)

                if(WildMatch([ITEM ID],'*D*',mid([Account Number],5,8),([Account Number])) as INsite_Acct,

                • Re: CASE statement in QlikView
                  Joshua Russin

                  I am trying to do this, but trying to use some of the same values for different cases.

                   

                  Ex.

                   

                  =if(WildMatch(SaTy_OTIF,'2MEM','2MMO','NB2','ZBPU','ZD2S','ZKR'),'Internal / Warehouse to Warehouse',

                  if(WildMatch(SaTy_OTIF,'ZEU1','ZIN1'),'OUS',

                      if(WildMatch(SaTy_OTIF,'OR','UB'),'Domestic',

                      if(WildMatch(SaTy_OTIF,'2MEM','2MMO','NB2','ZBPU','ZD2S','ZKR','ZEU1','ZIN1','OR','UB'),'test'))))

                   

                  This only shows the first 3 and not the "total" statement.

                • Re: CASE statement in QlikView
                  kalyan satya

                  case

                  when saleschannel='Online' and (userid is null  or userid not like '%RB:MOBILE%')  then 'ONLINE WEB SALES'

                  when saleschannel=online and userid like '%RB:MOBILE%' then 'MOBILE'

                  when userid like '%RB:MOBILE%'  and userid not in ('RB:MOBILEWINDOWASAPP','RB:MOBILEADRIODAPP')  then 'WAP SALES'

                  when userid like '%RB:MOBILE%'  and userid in ('RB:MOBILEWINDOWASAPP','RB:MOBILEADRIODAPP')  then  'APP SALES'

                  when saleschannel='corporate' and bookingsource='corporate' then 'offline agents'

                  when saleschannel='Corporate' and bookingsource='webdirect' then 'other offline agents'

                  end as channel

                   

                   

                  can you help out with this Query ..i am facing problem  in writing (in function)  and not like

                    • Re: CASE statement in QlikView
                      Dok Mbini

                      Hi All,

                       

                      I've been also struggling to convert this piece of code to Qlikview,

                      Could I please get assistance:

                       

                      CASE

                        when MAC_CODE = 963 then ''SNL''

                        Else ''MLOAN''

                      END as PRODUCT_CODE

                       

                      CASE

                           when REGISTRATION_MONTH_CODE = ''NOTREG''

                             then ''N''

                           else

                                  CASE when ( year(INFORMATION_DATE)*100 + month(INFORMATION_DATE) )

                                            else ''N''

                                  END as  REG_LAST_3MONTHS_IND

                       

                      CASE

                                when FINAL_ML_AMT <= 50000   then  ''A''

                                when FINAL_ML_AMT <= 100000  then  ''B''

                                when FINAL_ML_AMT <= 150000  then  ''C''

                                when FINAL_ML_AMT <= 350000  then  ''D''

                                when FINAL_ML_AMT <= 700000  then  ''E''

                                else ''F''

                                END as MLOANS_BANDS_CODE

                    • Re: CASE statement in QlikView
                      Asaf Meiri

                      fix:

                      =if(wildmatch([Account Name],'*Hong Kong*'), 'HK', if(wildmatch([Account Name],'*USA*'), 'NY', if(wildmatch([Account Name],'*UK*'),'LDN', 'OTHER')))