Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nicolas_martin
Contributor II

How to have numeric values in a cross table?

Hello,

I want to load the crosstable

2016-06-16_120759.jpg

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.

2016-06-16_120914.jpg

Why?

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

1 Solution

Accepted Solutions

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

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

Capture.PNG

4 Replies

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

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

Capture.PNG

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

Or

ForceNum:

LOAD

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

  Year AS Forced.Year_origin,

  MyField AS Forced.MyField,

  Value AS Forced.Value

Resident Cross;

MVP
MVP

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

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
Contributor II

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

Thank you!

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

Community Browser