topic Re: Loading a selected list under a Dimension Header. in QlikView

Loading a selected list under a Dimension Header.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Name ID

Code

Days

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.

Re: Loading a selected list under a Dimension Header.

devarasu07 — Tue, 28 Nov 2017 09:29:54 GMT

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');

Re: Loading a selected list under a Dimension Header.

devarasu07 — Tue, 28 Nov 2017 09:36:07 GMT

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)

Re: Loading a selected list under a Dimension Header.

jyothish8807 — Tue, 28 Nov 2017 09:36:19 GMT

Hi Don,

Load the data then create straight chart:

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

Exp: count([Name ID])

Re: Loading a selected list under a Dimension Header.

avkeep01 — Tue, 28 Nov 2017 09:36:46 GMT

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;