topic Re: Crosstable Alias Field in QlikView

Crosstable Alias Field

Anonymous — Fri, 17 Jun 2016 15:50:03 GMT

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 ...

Re: Crosstable Alias Field

swuehl — Fri, 17 Jun 2016 15:56:42 GMT

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;

Re: Crosstable Alias Field

sunny_talwar — Fri, 17 Jun 2016 15:57:16 GMT

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;

Re: Crosstable Alias Field

sunny_talwar — Fri, 17 Jun 2016 15:57:42 GMT

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

Re: Crosstable Alias Field

Anonymous — Fri, 17 Jun 2016 16:06:32 GMT

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

Re: Crosstable Alias Field

swuehl — Fri, 17 Jun 2016 16:28:09 GMT

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

Re: Crosstable Alias Field

sunny_talwar — Fri, 17 Jun 2016 16:32:04 GMT

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#().

Re: Crosstable Alias Field

swuehl — Fri, 17 Jun 2016 16:38:50 GMT

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.

Re: Crosstable Alias Field

sunny_talwar — Fri, 17 Jun 2016 16:42:16 GMT

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:

Re: Crosstable Alias Field

sunny_talwar — Fri, 17 Jun 2016 16:42:55 GMT

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