Add Column through Edit Script using SQL Select / IF

[#1]

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!

[Anonymous]

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....;

[#3]

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.

[Anonymous]

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

[#5]

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

[MayilVahanan]

Hi

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

[#7]

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

[#8 · Accepted solution]

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