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

[stevelord]

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.

[stevelord]

So, now my awesome workaround is export table box and do the rest in excel… at least I got those percents out of the cells.

I can give correct answer if someone can help finish resolving the straight table issue side. I’m giving helpful answer credit now. (Num functions works and I’m not going back to check the right() function since the num version of the expression works.)

From: Lord, Steve

Sent: Friday, September 13, 2013 3:08 PM

To: 'jive-1079998749-2hop-2-8e43@qliktech-public.hosted.jivesoftware.com'

Subject: RE: - Right(Subfield()) help needed, data not coming through on straight table

Ahh.. the bottom of the listbox was off the screen for part of this issue. So I get values 1-3 and all the rest on the list box.

I have a table box that is showing all of the values fine as well, so it’s down to troubleshooting the straight table.

[stevelord]

Actually, I don’t see how to do correct/helpful answer credit in this updated website version. I’ll give helpful credit to whoever tells me how to give helpful credit too. ☺

Helpful credit to treseco for helping me confirm the script formula is fine and giving one that doesn’t rely on character counts. The new table box makes the rest of my work possible in Excel at least. Still hoping for a straight table issue solution. ☺

[CELAMBARASAN]

Hi,

Expression value might be zero. Try by unchecking the suppress zero values option in the presentation tab.