cross table error

[arulsettu]

i am calculating budget amount. i am having budget amount month wise like this

BD_JAN,

     BD_FEB,

     BD_MAR,

     BD_APR,

     BD_MAY,

     BD_JUN,

     BD_JUL,

     BD_AUG,

     BD_SEP,

     BD_OCT,

     BD_NOV,

     BD_DEC

so i used cross table as per JAGAN suggested below

Temp:

CrossTable(Month, Data, 5)

LOAD COMP_CODE,

     LOB_CODE,

     BD_YEAR,

     BD_TYPE,

     BD_JAN,

     BD_FEB,

     BD_MAR,

     BD_APR,

     BD_MAY,

     BD_JUN,

     BD_JUL,

     BD_AUG,

     BD_SEP,

     BD_OCT,

     BD_NOV,

     BD_DEC,

     BD_CR_DT,

     BD_CR_UID

FROM

(qvd);

Budget:

LOAD

*,

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS Month,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

Resident Temp;

DROP TABLE Temp;

i am getting this error

Field names must be unique within table

Budget:

LOAD

*,

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS Month,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

Resident Temp

please help

[Anonymous]

Upload sample data so can help

[jagan]

Hi,

Change this code and check

Temp:

CrossTable(Month, Data, 4)

LOAD COMP_CODE,

     LOB_CODE,

     BD_YEAR,

     BD_TYPE,

     BD_JAN,

     BD_FEB,

     BD_MAR,

     BD_APR,

     BD_MAY,

     BD_JUN,

     BD_JUL,

     BD_AUG,

     BD_SEP,

     BD_OCT,

     BD_NOV,

     BD_DEC     

FROM

(qvd);

Regards,

Jagan.

[arulsettu]

thanks Kiran

thank you very much every one

[arulsettu]

Hi Jagan,

               MonthName field showing in 123 format how to show that in jan feb mar

thanks

[jagan]

Hi,

Try like this

Budget:

LOAD

*,

Month(Date) AS MonthName;

LOAD

*,

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') ASMonthNumber,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

Resident Temp;

[arulsettu]

thanks Jagan