12 Replies Latest reply: Sep 13, 2013 3:40 PM by Celambarasan Adhimulam RSS

    Right(Subfield()) help needed, data not coming through on straight table

    Steve Lord

      Hi I have data in some fields that I need to split out.

      The fields are ActivityA ActivityB ActivityC ActivityD ActivityE ActivityF

       

      The values on the excel worksheets look like 5 (12%), 132 (38%) etc.

       

      I have this script that I saw someone else use to split out one field and tried to use it to get the number and not the stuff in () in my fields:

      Right(SubField(ActivityA,'(',1),4) as nActivityA,

       

      I am actually using several together

           Right(SubField(ActivityA,'(',1),4) as nActivityA,

           Right(SubField([ActivityB],'(',1),4) as [nActivityB],

           Right(SubField(ActivityC,'(',1),4) as nActivityC,

           Right(SubField([ActivityD],'(',1),4) as [nActivityD],

           Right(SubField(ActivityE,'(',1),4) as nActivityE,

           Right(SubField([ActivityF,'(',1),4) as [nActivityF]

       

      Some of the values in these new fields appear on a new straight table I made, but most don't and I am wondering if QV is throwing out items with some kind of conflict.  The SubField function is new to me, so I'd appreciate any guidance.

       

      These formulas are on 20 tables for 20 excel worksheets from January 2012 to August 2013.  My table simply had clientaccountname, the fields created by the Right(SubField()) functions, and an expression to total the activity fields.  Script text is attached.