Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Qlik Load Else - IF

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.

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Qlik Load Else - IF

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

other fields are similiar to above

View solution in original post

3 Replies
Highlighted
Employee
Employee

Re: Qlik Load Else - IF

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

other fields are similiar to above

View solution in original post

Highlighted
Not applicable

Re: Qlik Load Else - IF

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;

Highlighted
Employee
Employee

Re: Qlik Load Else - IF

TEMP:

LOAD *;

SQL

SELECT UPPER(str_value) as 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');