Table of statistics in QV

[#1]

Hi,

I'm a new user to QV. Not sure how to solve the following issue, any experts can help?

I have a dataset in excel file as follows:

A	B	C
2	-5
-1	0	-3
	6	4

I want to import this file in QV and generate the following statistical table as a result:

Variables	Count	Count of Zeros	Count of Nulls	Count of negative values
A	3	0	0	1
B	3	1	0	1
C	3	0	1	1

Anyone know how to do it? Thanks!

[#2]

Hi,

Fixed it. I replaced the dummy field by row number, to keep values from the same row connected.

Data:

CrossTable (Variables, Value)

LOAD RowNo() as Rank, *; //Rank is row number in original array

LOAD * INLINE [

    A, B, C

    2, -5

    -1, 0, -3

    , 6, 4

];

left join(Data)

LOAD Rank, if(sum(if(Variables='A' and len(trim(Value))=0, 1))>0, 'Yes', 'No') as [A is null]

Resident Data

Group By Rank

;

Original row number is saved in the crosstable, so similar filters can be added the same way.

[#3]

Hi,

Yes, empty cells are interpreted differently depending on the source.

From QVD or Excel (formats where there are defined cells), it's taken as null values.

From reading text files, csv or inline load where cells are reconstituted, it's taken as empty strings.

The Len(Trim( formula is worth knowing

[Anonymous]

Thanks Pierre.   It does seem that QlikView handles Null any several different ways which can cause unexpected or unwanted results.  The Len(Trim formula is definitely useful.  Below is another method to get Value to display Null instead of an empty space so that the Original Formulas by Michael work as intended using Len(Trim to force Null Values.

-------------------------------------------------------------------

DataTemp:

CrossTable (Variables, Value)

LOAD null() AS Dummy, *;  // Dummy field is required in order to be able to pivot the columns

LOAD * INLINE [

    A, B, C

    2, -5

    -1, 0, -3

    , 6, 4

];

Data:

Load

Variables,

IF(Len(TRIM(Value))>0,Value) as Value

Resident DataTemp;

Drop Table DataTemp;

Results:

LOAD Variables,

  count(DISTINCT Value) AS Count,

  sum(if(Value=0, 1, 0)) AS CountZeroes,

  sum(if(isnull(Value), 1, 0)) AS CountNull,

  sum(if(Value<0, 1, 0)) AS CountNegative

Resident Data

Group by Variables;