Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try like:
Num(SubField(ActivityA, '(' , 1)) as nActivityA
That actually gives an identical result with the same issue when I try to get the data on the straight table. ☺
You can attach data of the excel
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.
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
89
87
73
57
47
45
40
37
25
Listbox has this:
413
224
206
192
186
181
179
154
150
142
137
135
131
116
112
111
105
89
87
84
78
73
64
57
56
54
53
50
47
46
45
41
40
38
37
35
34
33
31
30
28
27
26
25
24
23
22
21
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
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%)
2 (33%)
2 (33%)
4 (50%)
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%)
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.
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.)
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.