topic Re: How to have numeric values in a cross table? in QlikView

How to have numeric values in a cross table?

nicolas_martin — Thu, 16 Jun 2016 10:10:08 GMT

Hello,

I want to load the crosstable

I have the following script:

Source:

LOAD * INLINE [

MyField, 2010, 2011, 2012

Test, x, y, z

Test2, xx, yy, zz

];

Cross:

CrossTable(Year, Value)

LOAD

MyField,

[2010],

[2011],

[2012]

RESIDENT Source;

ForceNum:

LOAD

num(Year) AS Forced.Year,

Year AS Forced.Year_origin,

MyField AS Forced.MyField,

Value AS Forced.Value

Resident Cross;

I can't success to have the "Year" filed as a numeric value.

Even the "num(Year)" returns nothing.

Why?

How can I have my Year field as a numeric value?

Re: How to have numeric values in a cross table?

sunny_talwar — Thu, 16 Jun 2016 10:11:36 GMT

Try this:

ForceNum:

LOAD

num#(Year) AS Forced.Year,

Year AS Forced.Year_origin,

MyField AS Forced.MyField,

Value AS Forced.Value

Resident Cross;

UPDATE: Sample attached

Re: How to have numeric values in a cross table?

sunny_talwar — Thu, 16 Jun 2016 10:13:01 GMT

ForceNum:

LOAD

Date#(Year, 'YYYY') AS Forced.Year,

Year AS Forced.Year_origin,

MyField AS Forced.MyField,

Value AS Forced.Value

Resident Cross;

Re: How to have numeric values in a cross table?

swuehl — Thu, 16 Jun 2016 10:27:59 GMT

Why?

How can I have my Year field as a numeric value?

The CROSSTABLE LOAD prefix will transform your field names to text values, so you need to interpret numbers using an interpretation function like Num#().

Num() is a formatting function, to format the text representation of numbers (which you don't have).

I don't think that you want to use the Date#() interpretation function. It will return a numeric representation, but with a internal date value, which I seldom use in my applications (edit: for year fields).

Hope this helps to understand better.

Stefan

Re: How to have numeric values in a cross table?

nicolas_martin — Thu, 16 Jun 2016 11:29:53 GMT

Thank you!

I didn't knew remembered that CROSSTABLE prefix transformed field names to text values.