topic Re: Right(Subfield()) help needed, data not coming through on straight table in QlikView

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

stevelord — Fri, 13 Sep 2013 17:57:08 GMT

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.

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

tresB — Fri, 13 Sep 2013 18:05:42 GMT

Try like:

Num(SubField(ActivityA, '(' , 1)) as nActivityA

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

stevelord — Fri, 13 Sep 2013 18:15:53 GMT

That actually gives an identical result with the same issue when I try to get the data on the straight table. ☺

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

Anonymous — Fri, 13 Sep 2013 18:15:54 GMT

You can attach data of the excel

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

tresB — Fri, 13 Sep 2013 18:20:11 GMT

Do you get that in listbox? If yes, you know, it has to be something in the straight table, may be the dimension or the expression.

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

stevelord — Fri, 13 Sep 2013 18:32:05 GMT

Actually, I see the listbox dropping values below 4 even though I see 1 2 3s in that column on some of the worksheets. I’m wondering if 1 (12%) and 1 (33%) being in the same column on raw data would cause that 1 to be discarded altogether. And maybe straight table pooling the different sheets together amplifies the issue by bringing all of the different tables into the conflict.

Straight table has this:

413

206

192

186

181

179

154

150

137

131

112

111

105

Listbox has this:

413

224

206

192

186

181

179

154

150

142

137

135

131

116

112

111

105

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

stevelord — Fri, 13 Sep 2013 18:38:41 GMT

I can’t attach the whole actual file, but here’s a cut of the table data I am working with:

Each row is a different client, and I have 20 worksheets of these in an excel workbook that is the data source.

Each Worksheet is named for the month and year, and I have the months and years populated in script for each table. (Script text included in attachment.)

ActivityA

ActivityB

ActivityC

ActivtyD

ActifityE

ActivityF

38 (35.5%)

15 (14%)

14 (13.1%)

1(0.9%)

39 (36.4%)

6 (27.3%)

5 (22.7%)

1(4.5%)

9 (40.9%)

2 (28.6%)

3 (50%)

2 (33%)

6 (100%)

2 (33%)

4 (50%)

2 (100%)

3 (75%)

1 (25%)

8 (36.4%)

2 (9.1%)

6 (27.3%)

1 (4.5%)

5 (22.7%)

1 (14.3%)

3(42.9%)

1 (14.3%)

2 (28.6%)

6 (35.3%)

2 (11.8%)

4 (23.5%)

5 (29.4%)

2 (100%)

2 (66.6%)

1 (33.3%)

186 (51%)

29 (7.9%)

19 (5.2%)

15 (4.1%)

10 (2.7%)

106 (29%)

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

tresB — Fri, 13 Sep 2013 18:42:58 GMT

I am not sure what do you mean by 'below 4' ? If you mean that not pulling more than 3 digit numbers, then that is because of your right() function in the formula. Use the one i suggested, that should resolve the issue of data in listbox. And if it does, you can be sure that there is nothing wrong in the script to pull the right data. Only thing, have to look into is the strraight table dimension/expression or re-visit the data.

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

stevelord — Fri, 13 Sep 2013 19:02:05 GMT

I’m going to look at straight table dimensions now. Maybe adding months and years and such will help the table sort out the data it is currently discarding.

For the listbox, I mean it is throwing out the actual number values of between 1 and 3 and I wonder if it’s because those appear more than once but with different percentages beside them on a given table. (i.e. because of a perceived conflict on the raw data as it is going into qlikview.)

Either way, I will experiment more with table dimensions and try both formulas again. (I don’t recall if the right() formula was throwing out the values before they got to the listbox too.)

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

stevelord — Fri, 13 Sep 2013 19:08:05 GMT

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.

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

stevelord — Fri, 13 Sep 2013 19:11:05 GMT

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.

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

stevelord — Fri, 13 Sep 2013 19:26:05 GMT

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. ☺

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

CELAMBARASAN — Fri, 13 Sep 2013 19:40:31 GMT

Hi,

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