Qlik Load Else - IF

Mon, 25 Jul 2016 17:57:21 GMT

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 — Mon, 25 Jul 2016 18:11:46 GMT

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

other fields are similiar to above

Re: Qlik Load Else - IF

Mon, 25 Jul 2016 19:51:10 GMT

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;

Re: Qlik Load Else - IF

Clever_Anjos — Mon, 25 Jul 2016 20:15:39 GMT

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

topic Qlik Load Else - IF in QlikView

Qlik Load Else - IF

Re: Qlik Load Else - IF

Re: Qlik Load Else - IF

Re: Qlik Load Else - IF