Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add Column through Edit Script using SQL Select / IF

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

7 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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.2342312Requirement
TSR.23424232Worksheet
REQ.234234215323Requirement
Not applicable
Author

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