3 Replies Latest reply: Jul 25, 2016 4:15 PM by Clever Anjos RSS

    Qlik Load Else - IF

    Azmina Virani

      I have a SQL Case statement that I want to do in QLIK.

       

      The goal is to have a QVD that saves the SQL data and then do the Case statement in SQL.

      I have this right now:

       

       

      case when  (str_value like 'app=mobile%') or (str_value like 'app=Account Center; platform=android%') or (str_value like 'app=Account Center; platform=ios%')

                         then 'MOBILE=YES' else 'MOBILE=NO' end as mobile_flag,

                  case when str_value like 'app=acquisition%' then 'ACQUISITION=YES' else 'ACQUISITION=NO' end as acquisition_flag,

                  case when str_value like 'app=activation%' then 'ACTIVATION=YES' else 'ACTIVATION=NO' end as activation_flag,

                  case when (str_value like 'app=account-center-ui%') or (str_value like 'app=Account Center; platform=web%')

                                     or (str_value like 'Web') then 'WEB=YES' else 'WEB=NO' end as web_flag,

       

                  case when upper(str_value) like '%IOS%' then 'IOS=YES'

                       when upper(str_value) like '%ANDROID%' then 'IOS=NO'

                       when upper(str_value) like '%UNDEFINED%' then 'IOS=NO'

                         else 'NON-MOBILE' end as ios_platform_flag,

       

                  case when upper(str_value) like '%ANDROID%' then 'ANDROID=YES'

                         when upper(str_value) like '%IOS%' then 'ANDROID=NO'

                         when upper(str_value) like '%UNDEFINED%' then 'ANDROID=NO'

                         else 'NON-MOBILE' end as android_platform_flag,

       

                  case when str_value like 'app=account-center-ui%' then 'New OAC'

                         when str_value like 'app=acquisition%' then 'Acquisition v4'

                         when str_value like 'app=activation%' then 'Activation v4'

                         when str_value like 'app=mobile%' then 'Mobile App v2'

                         when str_value like 'app=Account Center; platform=android%' then 'Mobile App v4 - Android'

                         when str_value like 'app=Account Center; platform=ios%' then 'Mobile App v4 - iOs'

                         when str_value like 'app=Account Center; platform=web%' then 'OAC v4'

                         when str_value like 'Web' then 'OAC 2/3'

                         else 'Error - Change Made'

                         end as source

      from  netspend.web_action_log C

      WHERE  log_time >= to_date('10/01/2015','mm/dd/yyyy')

      and log_time <  to_date('10/02/2015','mm/dd/yyyy')

       

       

       

      I WANT TO DO SOMETHING LIKE THIS:

       

       

      TEMP:

      SQL

      SELECT str_value

      from  netspend.web_action_log C

      WHERE  log_time >= to_date('10/01/2015','mm/dd/yyyy')

      and log_time <  to_date('10/02/2015','mm/dd/yyyy');

       

      LOAD

          if  str_value like 'app=acquisition%' then 'ACQUISITION=YES'

              else 'ACQUISITION=NO' end as acquisition_flag,

          if str_value like 'app=activation%' then 'ACTIVATION=YES'

              else 'ACTIVATION=NO' end as activation_flag,

          if upper(str_value) like '%IOS%' then 'IOS=YES'

          elseif  upper(str_value) like '%ANDROID%' then 'IOS=NO'

          elseif upper(str_value) like '%UNDEFINED%' then 'IOS=NO'

              else 'NON-MOBILE' end as ios_platform_flag,

      Resident Temp;

       

       

      ANY HELP WILL BE APPRECIATED.

        • Re: Qlik Load Else - IF
          Clever Anjos

          If ( wildmatch(str_value , 'app=acquisition*') , 'ACQUISITION=YES','ACQUISITION=NO' ) as acquisition_flag,

           

          other fields are similiar to above

            • Re: Qlik Load Else - IF
              Azmina Virani

              Getting the following error

               

              The following error occurred:

              Field not found - <str_value>

               

              Here is the script:

              TEMP:

              LOAD *;

              SQL

              SELECT UPPER(str_value)

              from  netspend.web_action_log C

              WHERE  log_time >= to_date('10/01/2015','mm/dd/yyyy')

              and log_time <  to_date('10/02/2015','mm/dd/yyyy');

               

               

              LOAD

              If ( wildmatch(str_value , 'app=acquisition*') , 'ACQUISITION=YES','ACQUISITION=NO' ) as acquisition_flag

              Resident TEMP;