Crosstable Alias Field

[Anonymous]

I am doing a crosstable load (below) to convert a pivot-type table to rows of data I can use for analysis.

The numbers you see below are days of a month. What I want to do is add formatting so the date is produced as, MM/DD/YYYY

I have tried Crosstable (('01/'&Day&'/2016') as Day , Service,2) but it hasnt worked.

Is there any way to alias the fields in a crosstable load?

Thank you,

Crosstable (Day, Service,2)

LOAD [F3],

     [F2], 

     [1],

     [2],

     [3],

     [4],

     [5],

     [6],

     [7],

     [8],

     [9],

     [10],

     [11],

     [12],

     [13],

     [14],

     [15],

     [16],

     [17],

     [18],

     [19],

     [20],

     [21],

     [22],

     [23],

     [24],

     [25],

     [26],

     [27],

     [28],

     [29],

     [30],

     [31]

FROM ...

[swuehl]

The CROSSTABLE LOAD will transform your field names to text values, so you need to do a RESIDENT LOAD of the resulting table and interprete the day numbers:

CROSS:

CROSSTABLE (Day, Service, 2)

LOAD ....;

RESULT:

NOCONCATENATE

LOAD F3,F2,

     Makedate(2016,1,Day) as Date,     

     Service

RESIDENT CROSS;

DROP TABLE CROSS;

[sunny_talwar · Accepted solution]

Try this:

Table:

Crosstable (Day, Service,2)

LOAD [F3],

     [F2],

     [1],

     [2],

     [3],

     [4],

     [5],

     [6],

     [7],

     [8],

     [9],

     [10],

     [11],

     [12],

     [13],

     [14],

     [15],

     [16],

     [17],

     [18],

     [19],

     [20],

     [21],

     [22],

     [23],

     [24],

     [25],

     [26],

     [27],

     [28],

     [29],

     [30],

     [31]

FROM ...

FinalTable:

NoConcatenate

LOAD F3,

          F2,

          Service,

          Date(MakeDate(2016, 1, Num#(Day))) as Date

FROM Table;

DROP Table Table;

[sunny_talwar]

Won't we need Num#(Day) here?

[Anonymous]

thanks to swhuel and sunny - sunny was correct, we just needed Num#(Day) to complete

[swuehl]

I assumed an implicte number interpretation by the Makedate() function - but haven't tested it.

[sunny_talwar]

I doubt it would, because if MakeDate() was able to do it, then why not just a simple Day without Num#() would do as well? I might be wrong, as I have not tested this myself. But I believe that anywhere after a crosstable, a number would need Num#().

[swuehl]

As I understand

Automatic Number Interpretation

If the function expects a number as argument, any text value that can be interpreted as number should do.

For example, this works

=Makedate('2016',1,'5')

But apparently, this hasn't worked (still haven't tested it) so we can add this to the odd behaviour of QV value interpretation.

[sunny_talwar]

Wouldn't this be true for this as well:

'5' as Number

will be read as number instead of text.

I agree that it should automatically interpret it, but I think it isn't doing it. Here is a test I ran:

Table:

CrossTable(Day, Value)

LOAD * Inline [

ID, 1, 2, 3, 4

1, 20,30,40, 50

2, 60, 70, 80, 90

3, 10, 10, 20, 32

];

FinalTable:

LOAD ID,

  Value,

  Date(MakeDate(2016, 1, Day)) as Date1,

  Date(MakeDate(2016, 1, Num#(Day))) as Date2

Resident Table;

DROP Table Table;

and got this:

[sunny_talwar]

Thanks for the link Stefan. This will be good link to go through