Print Page - Create a column with a positional value

Title: Create a column with a positional value
Post by: samroe1968 on 02 Feb 2011 06:29:52 AM

Hello

I am new to Framework Manager and I was wondering if anyone could help me.

I have a column that has the following information 'ChannelSheet¬General Insurance (APE £000's)' or 'FSGISummarySheet¬Home' and a few others.

I want to create a new column with just the bit before the ¬ ie Channel Sheet or FSGI SummarySheet and was wondering how to do it. I have tried to do left function of the position of the ¬ but it says that the function is not supported.

Title: Re: Create a column with a positional value
Post by: MFGF on 02 Feb 2011 07:01:09 AM

Hi Sam,

You have two challenges here - first you need to locate which character position the '¬' character occupies in the value. Then you need to extract leading part of the string before this position.

You could try something along the lines of the following:

substring([Your query item],1,position('¬',[Your query item])-1)

In the above, the position() function returns the character position of the ¬ character, and this is then used in a substring() function to extract from character one to the position immediately before ¬.

Hope this helps,

MF.

Title: Re: Create a column with a positional value
Post by: samroe1968 on 02 Feb 2011 09:08:56 AM

Thanks for that.

I have tried it and get the following error message - any ideas?

Cheers

This is what I have typed in:

select distinct
   substring([CSSR_CUSTOM_GROUP],1,position('¬',[CSSR_CUSTOM_GROUP])-1)

from
   [CFS Sales Summary Report NEW]."CSSR Data.csv"

and this is the error message I am getting:

This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 2: Syntax error near ",".

--------------------------------------------------------------------------------

Title: Re: Create a column with a positional value
Post by: MFGF on 02 Feb 2011 10:27:42 AM

Whoa! Hang on a moment - you're coding a SQL query there by the looks of things. The syntax I have given you is for a calculation within a query subject. Just leave the SQL for the query subject as select * from table. Either add the calculation on the Calculations tab of the query subject, or (if you're adhering to best practice guidelines) add it as an item to the model query subject based on this one up in the second modelling tier.

Regards,

MF.

Title: Re: Create a column with a positional value
Post by: samroe1968 on 03 Feb 2011 06:34:36 AM

:) Managed to get it too work

Thanks for you help, its much appreciated.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: samroe1968 on 02 Feb 2011 06:29:52 AM