how to handle two parts which need crosstable

[Anonymous]

Hi all,

I have a table which contains QTY and AMT with MONTH in column name.

Please refer to the attached Excel file

I would like to convert it to below structure:

Product	Month	QTY	AMT
A	JAN	10	20
A	JAN	2	13
A	JAN	4	11
A	JAN	7	23

Best Regards,

Louis

[marcus_sommer]

You could just use two The Crosstable Loads - one for QTY and one for AMT - and afterwards you could do a join over Product and Month.

- Marcus

[brunobertels]

Hi

Try this as script

//just load quantity part

   crosstable(Month,Quantity,1)
   tmpdata:
   load
    Product,
    "QTY JUL",
    "QTY AUG",
    "QTY SEP",
    "QTY OCT",
    "QTY NOV",
    "QTY DEC",
    "QTY JAN",
    "QTY FEB",
    "QTY MAR",
    "QTY APR",
    "QTY MAY",
    "QTY JUN"
FROM [lib://Bureau/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

quantity:
Load*,
date#(right("Month",3),'MMM')as Month1 // create Month dimension

resident tmpdata;
drop table tmpdata;
drop field Month;

//just load amount part

   crosstable(Month,Amount,1)
    tmpdata1:
   load
    Product,
   "AMT JUL",
    "AMT AUG",
    "AMT SEP",
    "AMT OCT",
    "AMT NOV",
    "AMT DEC",
    "AMT JAN",
    "AMT FEB",
    "AMT MAR",
    "AMT APR",
    "AMT MAY",
    "AMT JUN"
FROM [lib://Bureau/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

amount:
Load*,

date#(right("Month",3),'MMM')as Month1 create Month dimension

resident tmpdata1;
drop table tmpdata1;
drop field Month;

// Then join the two table quantity and amount

noconcatenate
final:
Load*
Resident amount;

join(final)
load*
resident quantity;
drop table amount,quantity