topic Change from straight table to pivot table creates 'null' values in QlikView

Change from straight table to pivot table creates 'null' values

Mon, 01 Oct 2012 18:23:08 GMT

This is very curious to me. I have a straight table that shows integer values >= 0 for all entries. Some of the entries, but not all, have sub-entries. eg I

have Aroute with two subsidiaries, Aiut Montreal and Aiut Toronto. In the straight table, it shows positive values for three fields, Capital, Customers, and Exmployees for both subsidiaries. It shows 0 - number - for for the four other fields in the table for these two companies.

If I change the table into a pivot table, and show it fully expanded, the values for Cap, Cust, and Emp show up exactly the same as they do for the straight table. However, if I collapse the pivot table to show only the parent company, these three values suddenly become 'null'. This is highly confusing - can anyone suggest why? Thanks.

Re: Change from straight table to pivot table creates 'null' values

swuehl — Mon, 01 Oct 2012 18:28:52 GMT

Maybe your expression can't be evaluated to an unambiguous value when collapsed? What are you using as expressions? Do you use aggregation functions in your expressions?

Re: Change from straight table to pivot table creates 'null' values

Mon, 01 Oct 2012 18:54:04 GMT

That's what's so confusing - they are not expressions. I do all that work in my SQL statement, e.g.:

(SELECT

COALESCE(COUNT(EMPLOYEES.ID),0)

FROM dbo.Employees

WHERE

Employees.StartDate >= '$(vReportStart)'

AND

Employees.StartDate <= '$(vReportEnd)'

) as EMP

So the value should always be an integer; if there were no employees added, the COALESCE function converts the null value to 0. How could I use an aggr expression on what should just be an integer? If you have a suggestion, I'll try it!

Re: Change from straight table to pivot table creates 'null' values

swuehl — Mon, 01 Oct 2012 19:08:55 GMT

Not sure if I understand.

Well, you've got two values e.g. for expression Cust, 51 and 32, for dimension value AIROUTE.

If collapsed, what do you expect to see then? The sum of both values? Then use

=sum(YOURFIELDUSED)

Or avg(), max(), min() etc.

Re: Change from straight table to pivot table creates 'null' values

Mon, 01 Oct 2012 19:16:29 GMT

Thank you, my friend, you are correct as always.