7 Replies Latest reply: Aug 16, 2012 12:58 PM by Eric Schmid RSS

    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!

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

          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

            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
                Johannes Sunden

                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

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

                      • Re: Add Column through Edit Script using SQL Select / IF
                        Mayil Vahanan Ramasamy

                        Hi

                         

                             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

                            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
                              • 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