topic Crosstable Reference Field in QlikView

Crosstable Reference Field

Thu, 25 Sep 2014 07:33:46 GMT

Hi all

I have added a field {AddYears(TRAINEESTARTDATE,TRAINEECONTRACTYEARS) AS "Trainee End Date"} to a table.

The TRAINEECONTRACTYEARS field is in a crosstable and this causes issues. I can't even open the application after adding this new field due to the crossed tabled reference field. How can I resolve this without affecting the current structure of the script?

Re: Crosstable Reference Field

jagan — Thu, 25 Sep 2014 07:39:19 GMT

Hi,

Can you attach the script you are using?

Regards,

Jagan.

Re: Crosstable Reference Field

Thu, 25 Sep 2014 07:49:47 GMT

Hi Jagan

Script:

T_TRAINEES:
load distinct
TRAINEEBIUNIQUEID, //join
replace(replace(replace(replace(replace(replace(ASSESSORNAME,'CN=',''),'/OU=ZA/OU=ABAS/O=PwC',''),'/OU=ZA/OU=FIN/O=PwC',''),'/OU=ZA/OU=HR/O=PwC',''),'/OU=ZA/OU=Visitor/O=PwC',''),'/OU=ZA/OU=GTS/O=PwC','') As "Assessor",
replace(replace(replace(replace(replace(replace(EVALUATORNAME,'CN=',''),'/OU=ZA/OU=ABAS/O=PwC',''),'/OU=ZA/OU=FIN/O=PwC',''),'/OU=ZA/OU=HR/O=PwC',''),'/OU=ZA/OU=Visitor/O=PwC',''),'/OU=ZA/OU=GTS/O=PwC','') As "Evaluator",
OVERYR3TOTCOREHRS As "TRAINEE_Overyr3totcorehrs",
OVERYR3TOTHRS   As "TRAINEE_Overyr3tothrs",
OVERYR4TOTCOREHRS As "TRAINEE_Overyr4totcorehrs",
OVERYR4TOTHRS   As "TRAINEE_Overyr4tothrs",
OVERYR5TOTCOREHRS As "TRAINEE_Overyr5totcorehrs",
OVERYR5TOTHRS   As "TRAINEE_Overyr5tothrs",
STARTPERIOD    As "Trainee Start Period",
TRAINEECONTRACTYEARS As "Contract Yrs",
YEAR5MONTH04    As "Year5month4",
YEAR5MONTH04COREHRS As "Year5month4corehrs",
YEAR5MONTH04TOTHRS As "Year5month4tothrs",
YEAR5MONTH05    As "Year5month5",
YEAR5MONTH05COREHRS As "Year5month5corehrs",
YEAR5MONTH05TOTHRS As "Year5month5tothrs",
YEAR5MONTH06    As "Year5month6",
YEAR5MONTH06COREHRS As "Year5month6corehrs",
YEAR5MONTH06TOTHRS As "Year5month6tothrs",
YEAR5MONTH07    As "Year5month7",
YEAR5MONTH07COREHRS As "Year5month7corehrs",
YEAR5MONTH07TOTHRS As "Year5month7tothrs",
YEAR5MONTH08    As "Year5month8",
YEAR5MONTH08COREHRS As "Year5month8corehrs",
YEAR5MONTH08TOTHRS As "Year5month8tothrs",
YEAR5MONTH09    As "Year5month9",
YEAR5MONTH09COREHRS As "Year5month9corehrs",
YEAR5MONTH09TOTHRS As "Year5month9tothrs",
//YEAR5TOTCOREHRS   As "Year5totcorehrs",
//YEAR5TOTHRS    As "Year5tothrs",
floor(DATECREATION) as "Trainee Creation Date",
//Convert(Varchar(10),[DATECREATION],120) As "Trainee Creation Date"
AddYears(TRAINEESTARTDATE,TRAINEECONTRACTYEARS) AS "Trainee End Date"
resident TRAINEE_TEMP;

drop table TRAINEE_TEMP;

TRAINEE_HRS:
CROSSTABLE (MonthNo, MonthYr, 2)
LOAD
TRAINEEBIUNIQUEID,
"Contract Yrs",
$(vMonth)
RESIDENT T_TRAINEES;

TOTALHRS:
CROSSTABLE (MonthNo, TotalHrs, 2)
LOAD
TRAINEEBIUNIQUEID,
"Contract Yrs",
$(vTotHrs)
RESIDENT T_TRAINEES;

COREHRS:
CROSSTABLE (MonthNo, CoreHrs, 2)
LOAD
TRAINEEBIUNIQUEID,
"Contract Yrs",
$(vCoreHrs)
RESIDENT T_TRAINEES;

Re: Crosstable Reference Field

jagan — Thu, 25 Sep 2014 07:56:13 GMT

Hi,

Can you explain what you are trying to do with your actual data and your expected output?

Regards,

Jagan

Re: Crosstable Reference Field

Thu, 25 Sep 2014 08:20:09 GMT

The entire sript worked fine until I added the AddYears(TRAINEESTARTDATE,TRAINEECONTRACTYEARS) AS "Trainee End Date". I just want to add this one field to the script and add it to multibox in the front end.

Re: Crosstable Reference Field

jagan — Thu, 25 Sep 2014 08:53:03 GMT

Hi,

I think that would not be the issue, try loading the script upto that table by giving

Exit Script;

If it reloads then that is not the problem.

Regards,

Jagan.

Re: Crosstable Reference Field

Thu, 25 Sep 2014 09:09:27 GMT

Thank you, I will try that.