How to have numeric values in a cross table?

[nicolas_martin]

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?

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

Or

ForceNum:

LOAD

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

  Year AS Forced.Year_origin,

  MyField AS Forced.MyField,

  Value AS Forced.Value

Resident Cross;

[swuehl]

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

[nicolas_martin]

Thank you!

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