Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Crosstable Reference Field

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?

6 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach the script you are using?

Regards,

Jagan.

Not applicable
Author


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;

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan

Not applicable
Author

Hi

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.

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

Thank you, I will try that.