Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Loading a selected list under a Dimension Header.

Name IDCodeDays

 

10
25
30
35
45
53
61
69
77
85
93
101
109
117
125
133
141
149
157
165
173
181
189
197
205
213
221
229
237
245
253
261
269

 

Alpha
Bravo
Charley
Delta
Lima
Hotel
Alpha
Bravo
Charley
Delta
Lima
Hotel
Alpha
Bravo
Charley
Delta
Lima
Hotel
Alpha
Bravo
Charley
Delta
Lima
Hotel
Alpha
Bravo
Charley
Delta
Lima
Hotel
Alpha
Bravo
Charley

 

5
10
15
5
10
10.5
11
11.5
12
12.5
13
13.5
14
14.5
15
15.5
16
16.5
17
17.5
18
18.5
19
19.5
20
20.5
21
21.5
22
22.5
23
23.5
24

I am trying to load the above table and want the column code as my dimension, but I don't want all the rows under my dimension header. I just want Alpha, Bravo, Charley and Delta. My expressions would be the count of Name ID and Days Column.

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Hi Don,

Load the data then create straight chart:

Dimension: =if(WildMatch(Code,'Alpha',' Bravo', 'Charley','Delta'),Code)

Exp: count([Name ID])

Best Regards,
KC

View solution in original post

4 Replies
devarasu07
Master II
Master II

Hi

Try like below,

Fact:

LOAD * INLINE [

    Name ID, Code, Days

    10, Alpha, 5

    25, Bravo, 10

    30, Charley, 15

    35, Delta, 5

    45, Lima, 10

    53, Hotel, 10.5

    61, Alpha, 11

    69, Bravo, 11.5

    77, Charley, 12

    85, Delta, 12.5

    93, Lima, 13

    101, Hotel, 13.5

    109, Alpha, 14

    117, Bravo, 14.5

    125, Charley, 15

    133, Delta, 15.5

    141, Lima, 16

    149, Hotel, 16.5

    157, Alpha, 17

    165, Bravo, 17.5

    173, Charley, 18

    181, Delta, 18.5

    189, Lima, 19

    197, Hotel, 19.5

    205, Alpha, 20

    213, Bravo, 20.5

    221, Charley, 21

    229, Delta, 21.5

    237, Lima, 22

    245, Hotel, 22.5

    253, Alpha, 23

    261, Bravo, 23.5

    269, Charley, 24

] where Match(Code,'Alpha', 'Bravo', 'Charley','Delta');

devarasu07
Master II
Master II

Hi,

Refer the attached sample,

another method using set analysis

count( distinct {$<Code={'Alpha', 'Bravo', 'Charley','Delta'}>}[Name ID])

sum(  {$<Code={'Alpha', 'Bravo', 'Charley','Delta'}>}Days)

Capture.JPG

jyothish8807
Master II
Master II

Hi Don,

Load the data then create straight chart:

Dimension: =if(WildMatch(Code,'Alpha',' Bravo', 'Charley','Delta'),Code)

Exp: count([Name ID])

Best Regards,
KC
avkeep01
Partner - Specialist
Partner - Specialist

Hi Don,

You could use a crosstable. By the way, maybe you don't want to use count, but sum in the expression in the last table.

Original:

LOAD Code, [Name ID], Days INLINE [

Name ID, Code, Days

10, Alpha, 5

25, Bravo, 10

30, Charley, 15

35, Delta, 5

45, Lima, 10

53, Hotel, 10.5

61, Alpha, 11

69, Bravo, 11.5

77, Charley, 12

85, Delta, 12.5

93, Lima, 13

101, Hotel, 13.5

109, Alpha, 14

117, Bravo, 14.5

125, Charley, 15

133, Delta, 15.5

141, Lima, 16

149, Hotel, 16.5

157, Alpha, 17

165, Bravo, 17.5

173, Charley, 18

181, Delta, 18.5

189, Lima, 19

197, Hotel, 19.5

205, Alpha, 20

213, Bravo, 20.5

221, Charley, 21

229, Delta, 21.5

237, Lima, 22

245, Hotel, 22.5

253, Alpha, 23

261, Bravo, 23.5

269, Charley, 24

];

CrossTable:

CROSSTABLE (Dimension,Data,1) LOAD * RESIDENT Original;

DROP TABLE Original;

Result:

NOCONCATENATE LOAD

Code,

COUNT(IF(Dimension='Days',Data)) AS [# Days],

COUNT(IF(Dimension='Name ID',Data)) AS [# Name ID]

RESIDENT CrossTable

GROUP BY Code;

DROP TABLE CrossTable;