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]

Hi,

try:

Budget:th

LOAD

//specify fields instead of * / change Field month below to another name here,

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

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

Resident Temp

[Anonymous]

you are repeating Month field twice by giving * sign

[kiranmanoharrode]

Hi Arul,

You need to replace 5 with 4, because 4 field highlighted below are non cross table data fields.

and delete last two fields (underlined) from script for proper data transformation.

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);

Regards,
Kiran

[arulsettu]

hi thanks but the values for each month is not corect. any mistake in above code

thanks

[jagan · Accepted solution]

Hi Arul,

Try this script

Temp:

CrossTable(Month, Data, 6)

LOAD COMP_CODE,

     LOB_CODE,

     BD_YEAR,

     BD_TYPE,

     BD_CR_DT,

     BD_CR_UID,

     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);

Budget:

LOAD

*,

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

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 MonthName,

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]

Hi Kiran,

     i did like this

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);

Budget:

LOAD

*,

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

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

Resident Temp;

DROP TABLE Temp;

but the result is not correct

thanks for replying

[arulsettu]

Hi Jagan,

          please check this app cross table value for jan month and jan month value differs. please check it if i do any mistake

thanks

[arulsettu]

any help guys why the crosstable value for jan month and bugdet amount for jan month getting differ

its emergency please help

thanks

[Anonymous]

Try removing Right keyword