topic Re: Issues with missing values in QlikView

Issues with missing values

Thu, 14 Jul 2016 13:13:27 GMT

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

Re: Issues with missing values

sunny_talwar — Thu, 14 Jul 2016 13:42:05 GMT

Check for the second issue now

Re: Issues with missing values

Fri, 15 Jul 2016 12:22:03 GMT

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

sunny_talwar — Sat, 16 Jul 2016 03:32:09 GMT

Not the best of the solutions, but it works:

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

];

Pivot Table:

Dimensions:

Gender

Points

Job

Gender1

Expression:

Sum(If(Gender = Gender1, Salary))