Create a column with a positional value

[samroe1968]

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.

[MFGF]

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.

[samroe1968]

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 ",".

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

[MFGF]

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.

[samroe1968]

 :)  Managed to get it too work

Thanks for you help, its much appreciated.