Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Issues with missing values

Hi,

I have trouble including dimension values into pivot table. Please see attached example.

In Example 1 pivot table, I would like to see both genders for each job. Is it possible to achieve and how with given data model? If the model should be changed - how?

Another issue is that I'm trying to use alt states to exclude people from lists or calculations (same attachment). When I select someone to be excluded in Exclude Emplid listbox, this person is removed from Example 2 table. But there is a problem, that Grade and Points, that this person had, are also removed from other rows. How can avoid that?

I've tried searching for possible solutions with no luck, so any help or guidance is much appreciated

1 Solution

Accepted Solutions

Re: Issues with missing values

Not the best of the solutions, but it works:

Capture.PNG

Script:

Employee:

LOAD * INLINE [

Emplid, Name, Gender

111, John, M

113, Maria, F

123, Peter, M

124, Petra, F

321, Jason, M

126, Steve, M

221, Benjamin, M

432, Mark, M

998, Mikael, M

356, Sandra, F

];

Transactions:

LOAD * INLINE [

Emplid, Salary, Job

111, 500, aaa

113, 500, aaa

123, 600, bbb

124, 700, bbb

321, 800, ccc

126, 400, ddd

221, 300, bbb

432, 230, aaa

998, 400, ddd

356, 200, eee

];

Jobs:

LOAD * INLINE [

Job, Grade, Points

aaa, 12, 245

bbb, 13, 289

ccc, 14, 321

ddd, 14, 332

eee, 14, 332

];

Left Join (Jobs)

LOAD * Inline [

Gender1

M

F

];

Pivot Table:

Dimensions:

Gender

Points

Job

Gender1

Expression:

Sum(If(Gender = Gender1, Salary))

3 Replies

Re: Issues with missing values

Check for the second issue now

Capture.PNG

Not applicable

Re: Issues with missing values

Thank you @Sunny T, your solution worked. Although when I tried to exclude selected employees from the calculations in pivot table, I've stumbled upon same issues. Luckily I've found another way of excluding values selected in alternate state by using following code:

If(Len(Trim(GetFieldSelections(Emplid, ',',1, 'AS1'))) > 0,

Sum({$<Emplid=e({AS1}Emplid)>}Salary),

Sum(Salary)

)

However the first issue still remains

Re: Issues with missing values

Not the best of the solutions, but it works:

Capture.PNG

Script:

Employee:

LOAD * INLINE [

Emplid, Name, Gender

111, John, M

113, Maria, F

123, Peter, M

124, Petra, F

321, Jason, M

126, Steve, M

221, Benjamin, M

432, Mark, M

998, Mikael, M

356, Sandra, F

];

Transactions:

LOAD * INLINE [

Emplid, Salary, Job

111, 500, aaa

113, 500, aaa

123, 600, bbb

124, 700, bbb

321, 800, ccc

126, 400, ddd

221, 300, bbb

432, 230, aaa

998, 400, ddd

356, 200, eee

];

Jobs:

LOAD * INLINE [

Job, Grade, Points

aaa, 12, 245

bbb, 13, 289

ccc, 14, 321

ddd, 14, 332

eee, 14, 332

];

Left Join (Jobs)

LOAD * Inline [

Gender1

M

F

];

Pivot Table:

Dimensions:

Gender

Points

Job

Gender1

Expression:

Sum(If(Gender = Gender1, Salary))

Community Browser