topic cross table error in QlikView

cross table error

arulsettu — Mon, 06 Apr 2015 09:14:23 GMT

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

Re: cross table error

Anonymous — Mon, 06 Apr 2015 09:22:42 GMT

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

Re: cross table error

Anonymous — Mon, 06 Apr 2015 09:24:07 GMT

you are repeating Month field twice by giving * sign

Re: cross table error

kiranmanoharrode — Mon, 06 Apr 2015 09:27:03 GMT

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

Re: cross table error

arulsettu — Mon, 06 Apr 2015 09:31:31 GMT

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

thanks

Re: cross table error

jagan — Mon, 06 Apr 2015 09:32:49 GMT

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

Re: cross table error

arulsettu — Mon, 06 Apr 2015 09:33:59 GMT

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

Re: cross table error

arulsettu — Mon, 06 Apr 2015 09:39:11 GMT

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

Re: cross table error

arulsettu — Mon, 06 Apr 2015 09:48:35 GMT

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

its emergency please help

thanks

Re: cross table error

Anonymous — Mon, 06 Apr 2015 09:49:19 GMT

Try removing Right keyword

Re: cross table error

Anonymous — Mon, 06 Apr 2015 09:51:10 GMT

Upload sample data so can help

Re: cross table error

jagan — Mon, 06 Apr 2015 09:53:43 GMT

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.

Re: cross table error

arulsettu — Mon, 06 Apr 2015 10:04:14 GMT

thanks Kiran

thank you very much every one

Re: cross table error

arulsettu — Mon, 06 Apr 2015 10:14:01 GMT

Hi Jagan,

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

thanks

Re: cross table error

jagan — Mon, 06 Apr 2015 10:25:06 GMT

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;

Re: cross table error

arulsettu — Mon, 06 Apr 2015 10:52:02 GMT

thanks Jagan