3 Replies Latest reply on Jun 15, 2012 1:56 AM by mohammad.arsyad

    case function re-formatting

      Hi All,

       

      I am newbie in Tableau. I just trying to create new Calculated Field but it's getting an error. My previous query in our Oracle is like this :

      select case

                 when a.event_type_id in (1,5,7) then 'VOICE'

                 when a.event_type_id in (3,9) then 'SMS'

                 when a.event_type_id in (15) then 'GPRS'

                 when a.event_type_id in (37) and (upper(b.off_net_flag) like '%VOUCHERDATA%' or upper(b.off_net_flag) like '%DONGLE%'

                    OR upper(b.off_net_flag) like '%SKU%' or upper(b.off_net_flag) like '%PROXY%') then 'DATA'

                 when a.event_type_id in (37) and (upper(b.off_net_flag) like '%BB%' or upper(b.on_or_off) like '%BLACKBERRY%') then 'BB'

                 else 'VAS/OTHERS'

                end as category

      from first_table a, second_table b

       

      So, I try to re-format it like this in new Calculated Field :

        case [EVENT_TYPE_ID]

                 when  1 then 'VOICE'

                 when  5 then 'VOICE'

                 when  7 then 'VOICE'

                 when  3 then 'SMS'

                 when  9 then 'SMS'

                 when 15 then 'GPRS'

                 when 37 and (CONTAINS(upper([OFF_NET_FLAG]),'VOUCHERDATA') or CONTAINS(upper([OFF_NET_FLAG]),'DONGLE')

                              OR CONTAINS(upper([OFF_NET_FLAG]),'SKU') or CONTAINS(upper([OFF_NET_FLAG]),'PROXY')) then 'DATA'

                 when 37 and (CONTAINS(upper([OFF_NET_FLAG]),'BB') or CONTAINS(upper([ON_OR_OFF]),'BLACKBERRY')) then 'BB'

                 else 'VAS/OTHERS'

                end

       

      Can anyone help on this ?  Thanks in advance.

        • 1. Re: case function re-formatting
          Tracy Rodgers

          Hi Mohammad,

           

          It's hard to say what's going on with the calculation without knowing what all the fields are. Would you be able to post the workbook (twbx file) or send a couple of screenshots (with the errors, as well)?

           

          -Tracy

          • 2. Re: case function re-formatting

            Hi Tracy,

             

            I attach the screenshot for this issue. See the two red circles on this. I think Tableau doesn't permit "and" clause on it.

            Hopefully you can share with your experience on this.

            • 3. Re: case function re-formatting

              I just got the result. I use IF Function to solve this case.

               

              IF     [EVENT_TYPE_ID] = 1 OR [EVENT_TYPE_ID] = 5 OR [EVENT_TYPE_ID] = 7 THEN 'VOICE'

              ELSEIF [EVENT_TYPE_ID] = 3 OR [EVENT_TYPE_ID] = 9 THEN 'SMS'

              ELSEIF [EVENT_TYPE_ID] = 15 THEN 'GPRS'

              ELSEIF [EVENT_TYPE_ID] = 37 AND (CONTAINS(upper([OFF_NET_FLAG]),'BB') or      CONTAINS(upper([ON_OR_OFF]),'BLACKBERRY')) then 'BB'

              ELSEIF [EVENT_TYPE_ID] = 37 AND (CONTAINS(upper([OFF_NET_FLAG]),'VOUCHERDATA') or CONTAINS(upper([OFF_NET_FLAG]),'DONGLE')

                                      OR CONTAINS(upper([OFF_NET_FLAG]),'SKU') or CONTAINS(upper([OFF_NET_FLAG]),'PROXY')) THEN 'DATA'

              ELSE   'VAS/OTHERS'

              END

               

               

               

              Thanks