Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stevelord
Specialist
Specialist

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

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.

12 Replies
tresesco
MVP
MVP

Try like:

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

stevelord
Specialist
Specialist
Author

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

Anonymous
Not applicable

You can attach data of the excel

tresesco
MVP
MVP

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.

stevelord
Specialist
Specialist
Author

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

stevelord
Specialist
Specialist
Author

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%)

tresesco
MVP
MVP

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.

stevelord
Specialist
Specialist
Author

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

stevelord
Specialist
Specialist
Author

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.