topic Re: Re: Possibly Transpose Pivot Table? in QlikView

Possibly Transpose Pivot Table?

Thu, 16 Jan 2014 14:36:08 GMT

Hey guys,

I currently have a pivot table that counts the number of null fields with the expression:

count( if ( isNull(field1) or isNull(field2) or isNull(field3), field_to_be_counted)

this basically results in a table like

field1	field2	field3	Null Count
data	data	data	0
data	Null	data	1
data	Null	Null	2
data	data	data	0
Null	data	data	1

I'd like to get it to this arrangement (basically transpose the column names to the left side and hide the data and have the null count be down the column rather than across the row):

Column	Null Count
field1	1
field2	2
field3	1

Would this be possible?

Any help would be appreciated, thanks!

Re: Possibly Transpose Pivot Table?

kiranmanoharrode — Thu, 16 Jan 2014 14:59:53 GMT

Load Data and take resident of above table and

Try Below Script:

Fabs(IsNull(Field1))+Fabs(IsNull(Field2))+Fabs(IsNull(Field3)) as TotalNullCount

this will generate your total Null count

regards

Kiran Rode

+91 8976977897

Re: Possibly Transpose Pivot Table?

Thu, 16 Jan 2014 15:10:25 GMT

Thanks for the response Kiran!

I tried the code but I couldn't seem to find the TotalNullCount field. Could it possibly be because I loaded the data through a SQL query?

Re: Possibly Transpose Pivot Table?

gopalopsharma — Thu, 16 Jan 2014 19:19:10 GMT

Actually in my understanding there is no way to show a table in the format you wish. because charts/ pivot works on field values not the field names itself.

one work around.

while loading the data create three new fields like this

nullcount(Field1) as NullField1

nullcount(Field2) as NullField2

nullcount(Field3) as NullField3

Then create a straight table with no dimension but put each of above as three different expression and name the column names as desired.

Hope this helps.

Re: Re: Possibly Transpose Pivot Table?

preminqlik — Thu, 16 Jan 2014 19:59:25 GMT

hi , find attachment ..hope this helps you