Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
If ( wildmatch(str_value , 'app=acquisition*') , 'ACQUISITION=YES','ACQUISITION=NO' ) as acquisition_flag,
other fields are similiar to above
If ( wildmatch(str_value , 'app=acquisition*') , 'ACQUISITION=YES','ACQUISITION=NO' ) as acquisition_flag,
other fields are similiar to above
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;
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');