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: 
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
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

Check for the second issue now

Capture.PNG

Not applicable
Author

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

sunny_talwar

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