Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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

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

7 Replies
jsn
Honored Contributor

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

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

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

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.

jsn
Honored Contributor

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

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

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

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

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

Hi

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

Not applicable

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

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

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

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