topic Re: Add Column through Edit Script using SQL Select / IF in QlikView

Add Column through Edit Script using SQL Select / IF

Thu, 16 Aug 2012 04:24:07 GMT

I have a select statment pulling in multiple fields from various tables. One of the fields contains Alphanumeric codes as values of which I am trying to decode into a new column prior to loading the data. I assume this might be done using an IF statement, but have not had luck in doing so. For example, I start out with the following statment:

Event:

Select project_id, event_id, requirement_key, requirement_detail

from......;

I need to keep the values in requirement_key, but I also need to add a new field status_type that is populated with the results of the formula that decodes requirement key.

I'm thinking that it would look something like the below statement. The IF statement needs to look at the first three characters in every row of requirement_key. This will tell me if the row is a 'Worksheet' or a 'Requirement'. The results of the IF should populate the new column status_type in the same row as the decoded requirement_key. Unfortunately, I keep getting an error code 'missing right parenthesis' which doesn't make sense to me because I have three left and three right parenthesis.

Event:

Select project_id, event_id, requirement_key, requirement_detail, if((left(requirement_key,3)='TSR'),'Worksheet', 'Requirement') as status_type

from....;

I confirmed this worked loading an example through an Excel file so I'm thinking its a SQL syntax I'm not entering properly. Please help!

Re: Add Column through Edit Script using SQL Select / IF

Anonymous — Thu, 16 Aug 2012 04:33:47 GMT

Hi,

Try this:

Select project_id, event_id, requirement_key, requirement_detail, if(left(requirement_key,3)='TSR','Worksheet', 'Requirement') as status_type

from....;

Or potentially this:

Load

if(left(requirement_key,3)='TSR','Worksheet', 'Requirement') as status_type;

Select project_id, event_id, requirement_key, requirement_detail

from....;

Re: Add Column through Edit Script using SQL Select / IF

Thu, 16 Aug 2012 05:30:53 GMT

Thanks for your quick response Johannes. Results were not favorable this time, unfortunately:

This answer gave me the 'missing right parenthesis' error:

Select project_id, event_id, requirement_key, requirement_detail, if(left(requirement_key,3)='TSR','Worksheet', 'Requirement') as status_type

from....;

This answer gave me a 'field not found' error:

Load

if(left(requirement_key,3)='TSR','Worksheet', 'Requirement') as status_type;

Select project_id, event_id, requirement_key, requirement_detail

from....;

Since I couldnt get either to work, I attached a slightly modified example of the script I'm trying to add this IF statment too in hopes that helps provide more context to how you would suggest tackling this.

Re: Add Column through Edit Script using SQL Select / IF

Anonymous — Thu, 16 Aug 2012 05:45:24 GMT

Is this working for you?

Load

if(Left(e.requirement_key,3)='TSR', 'WorkSheet', 'Requirement') as status_type;

Select m.event_id, m.project_id, m.task_id, m.event_type, m.dttm_message_created, m.template_chosen,

pf.short_name client, IC.PRACTICE_NAME, IC.CATEGORY_NAME, IC.SUBCATEGORY_NAME, F.REGION_CODE,

e.requirement_key, e.prompt, e.prompt_detail, e.supplier_name, e.response

from SCMP.EVENT_INTEGRATION_MSG m, scmp.src_projects p, scmp.effort f, scmp.pt_client pf, SCMP.CATEGORIZATION_VW ic,

(select distinct req.event_id, req.requirement_key, req.prompt, req.prompt_detail, req.response_type, rs.supplier_name, resp.response

from scmp.event_requirement req, scmp.event_response resp, scmp.supplier rs

where resp.event_id = req.event_id

and resp.requirement_key = req.requirement_key

and resp.requirement_detail_key = req.requirement_detail_key

and rs.external_supplier_id = resp.external_supplier_id) e

Re: Add Column through Edit Script using SQL Select / IF

Thu, 16 Aug 2012 13:33:23 GMT

It doesn't. I get the error message of 'field not found' (attached screenshot).

Re: Add Column through Edit Script using SQL Select / IF

MayilVahanan — Thu, 16 Aug 2012 13:46:48 GMT

Can you able to say, which field in qlikview , you want to trim?? Fieldname in qlikview not sql?

Re: Add Column through Edit Script using SQL Select / IF

Thu, 16 Aug 2012 14:19:54 GMT

In QlikView the field appears as REQUIREMENT_KEY. I'm not trying to trim it though. I'm trying to analyze the first three characters within each of the values of that field to return a new corresponding value (either 'Worksheet' or 'Requirement') in a new column (Status_Type) within the same table. Below is an example of the desired result of my above IF statement.

REQUIREMENT_KEY	STATUS_TYPE
REQ.2342312	Requirement
TSR.23424232	Worksheet
REQ.234234215323	Requirement

Re: Add Column through Edit Script using SQL Select / IF

Thu, 16 Aug 2012 16:58:41 GMT

We determined the correct answer to be the below statement. Johannes provided the correct SQL syntax and Mayil triggered the thought of using the actual column header name from the raw data as it also appears in QlikView (REQUIREMENT_KEY) rather than the alias name (e.requirement_key) in the IF formula. Thank you everyone for your support!

Load

if(Left(REQUIREMENT_KEY,3)='TSR', 'WorkSheet', 'Requirement') as status_type;

Select m.event_id, m.project_id, m.task_id, m.event_type, m.dttm_message_created, m.template_chosen,

pf.short_name client, IC.PRACTICE_NAME, IC.CATEGORY_NAME, IC.SUBCATEGORY_NAME, F.REGION_CODE,

e.requirement_key, e.prompt, e.prompt_detail, e.supplier_name, e.response

from SCMP.EVENT_INTEGRATION_MSG m, scmp.src_projects p, scmp.effort f, scmp.pt_client pf, SCMP.CATEGORIZATION_VW ic,

(select distinct req.event_id, req.requirement_key, req.prompt, req.prompt_detail, req.response_type, rs.supplier_name, resp.response

from scmp.event_requirement req, scmp.event_response resp, scmp.supplier rs

where resp.event_id = req.event_id

and resp.requirement_key = req.requirement_key

and resp.requirement_detail_key = req.requirement_detail_key

and rs.external_supplier_id = resp.external_supplier_id) e