Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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

3 Replies
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

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;

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');

Community Browser