Joining table after preceding load

[Anonymous]

Hi!

I am trying to concatenate a teble after using preciding load, but I am getting this error: "Illegal combination of prefixes". Could someone explain me why? And how to join a table when using preceding load (I understand that the same thing can be done with resident load)?

This is my script:

test:
LOAD Field1,
Field3
FROM
$(vPath)\file.xlsx
(ooxml, embedded labels, table is sheet2);

Concatenate (test)
CrossTable(junk, Month, 2)
LOAD Field1,
Field2,
Month1,
Month2,
Month3,
Month4,
Month5,
Month6,
Month7,
Month8,
Month9,
Month10,
Month11,
Month12;


LOAD Field1,
Field2,
Month(Date#(1&'Jan', 'YMMM')) as Month1,
Month(Date#(1&'Feb', 'YMMM')) as Month2,
Month(Date#(1&'Mar', 'YMMM')) as Month3,
Month(Date#(1&'Apr', 'YMMM')) as Month4,
Month(Date#(1&'May', 'YMMM')) as Month5,
Month(Date#(1&'Jun', 'YMMM')) as Month6,
Month(Date#(1&'Jul', 'YMMM')) as Month7,
Month(Date#(1&'Aug', 'YMMM')) as Month8,
Month(Date#(1&'Sep', 'YMMM')) as Month9,
Month(Date#(1&'Oct', 'YMMM')) as Month10,
Month(Date#(1&'Nov', 'YMMM')) as Month11,
Month(Date#(1&'Dec', 'YMMM')) as Month12
FROM
$(vPath)\file.xlsx
(ooxml, embedded labels, table is sheet);

DROP Field junk;

Thank you in advance.

[its_anandrjs]

First you have to load the cross table and then create the resident load / QVD of that table to load the table

test:
LOAD Field1,
Field3
FROM
$(vPath)\file.xlsx
(ooxml, embedded labels, table is sheet2);

Tab2:

CrossTable(junk, Month, 2)
LOAD Field1,
Field2,
Month1,
Month2,
Month3,
Month4,
Month5,
Month6,
Month7,
Month8,
Month9,
Month10,
Month11,
Month12;

Concatenate(test)

Load * Resident Tab2;

Drop table Tab2;

[prieper · Accepted solution]

you cannot add a further specificiation to CROSSTABLE,

instead you need to make the crosstable first and then make a resident join or concatenation:

Temp: CROSSTABLE (Junk, Month, 2) LOAD ....;

CONCATENATE (test) LOAD * RESIDENT Temp;

DROP TABLE Temp;

Peter

[its_anandrjs]

For joining you can do this ways

test:
LOAD Field1, Field3 FROM
$(vPath)\file.xlsx
(ooxml, embedded labels, table is sheet2);

Tab2:

CrossTable(junk, Month, 2)
LOAD Field1,

Field2,
Month1,
Month2,
Month3,
Month4,
Month5,
Month6,
Month7,
Month8,
Month9,
Month10,
Month11,
Month12;

Concatenate(test)

Load * Resident Tab2;

Drop table Tab2;

LEFT JOIN (TEST)

LOAD Field1,
Field2,
Month(Date#(1&'Jan', 'YMMM')) as Month1,
Month(Date#(1&'Feb', 'YMMM')) as Month2,
Month(Date#(1&'Mar', 'YMMM')) as Month3,
Month(Date#(1&'Apr', 'YMMM')) as Month4,
Month(Date#(1&'May', 'YMMM')) as Month5,
Month(Date#(1&'Jun', 'YMMM')) as Month6,
Month(Date#(1&'Jul', 'YMMM')) as Month7,
Month(Date#(1&'Aug', 'YMMM')) as Month8,
Month(Date#(1&'Sep', 'YMMM')) as Month9,
Month(Date#(1&'Oct', 'YMMM')) as Month10,
Month(Date#(1&'Nov', 'YMMM')) as Month11,
Month(Date#(1&'Dec', 'YMMM')) as Month12
FROM
$(vPath)\file.xlsx
(ooxml, embedded labels, table is sheet);

DROP Field junk;