Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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
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;