Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am getting the below error : Field NAmes must be unique within table. But I don't see any duplicate NAmes.
Using preceding load
for Each vWorksheet in 'Quota 2013','Quota 2014'
QuotasTemp:
CrossTable(QuotaYear, QuotaQuarter, 2)
LOAD
EmployeeID as QuotaEmployeeID,
//SalesPerson,
//'2013' as QuotaYear,
SubField('$(vWorksheet)',' ',2) as QuotaYear,
Q1,
Q2,
Q3,
Q4
FROM [$(vPathExcel)Quota.xls]
(biff, embedded labels, header is 1 lines, table is [$(vWorksheet)$]);
Next
Quotas:
Load *,
QuotaEmployeeID & '|' & QuotaYear & '|' & QuotaQuarter as EmpYrQtr_Key
Resident QuotasTemp;
Drop Table QuotasTemp;
BounsTemp:
CrossTable(BonusQuarter, BounsAmount, 2)
LOAD
"YEAR",
EMPLOYEE_ID as EmployeeID,
QUARTER,
Q1,
Q2,
Q3,
Q4
FROM [$(vPathExcel)Bonus.xls]
(biff, embedded labels, header is 1 lines, table is Bonus$);
Bonus:
Load
EmployeeID,
BonusQuarter,
BounsAmount,
IF(IsNull("YEAR"),Peek(BounsYear),"YEAR") as BounsYear
Resident BounsTemp;
Drop Table BounsTemp;
Hi Mandue,
Just modify the first section like this and see if it is working for you ?
for Each vWorksheet in 'Quota 2013','Quota 2014'
QuotasTemp:
CrossTable(QuotaYear, QuotaQuarter, 2)
LOAD
EmployeeID as QuotaEmployeeID,
//SalesPerson,
//'2013' as QuotaYear,
SubField('$(vWorksheet)',' ',2) as QuotaYear_New,
Q1,
Q2,
Q3,
Q4
FROM [$(vPathExcel)Quota.xls]
(biff, embedded labels, header is 1 lines, table is [$(vWorksheet)$]);
Next
Please close the thread by marking the correct answer, if you have found the solution 🙂
Instead of Load * - Use All field names like Load Field, Field1, ....
I think this bit might be the problem:
QuotasTemp:
CrossTable(QuotaYear, QuotaQuarter, 2)
LOAD
EmployeeID as QuotaEmployeeID,
//SalesPerson,
//'2013' as QuotaYear,
SubField('$(vWorksheet)',' ',2) as QuotaYear,
Q1,
Q2,
Q3,
Q4
FROM [$(vPathExcel)Quota.xls]
(biff, embedded labels, header is 1 lines, table is [$(vWorksheet)$]);
You are loading a table with fields QuotaEmployeeID, QuotaYear, Q1, Q2, Q3, Q4, then your crosstable qualifies the first 2 fields (QuotaEmployeeID, QuotaYear) and tries to create 2 fields, 'QuotaYear' for Q1, Q2, Q3, Q4, and 'QuotaQuarter' for the data values. This would create a table with QuotaEmployeeID, QuotaYear, QuotaYear, and QuotaQuarter. This obviously isn't possible, because you'd have 2 QuotaYear fields. You can see this in the fourth line from the bottom in your log file as well.
To fix this I'd change your crosstable statement to something like (QuotaQuarter, Value, 2). This will put Q1, Q2, Q3, Q4 in the 'QuotaQuarter' field, and all respective values in the 'Value' field.
Hi Mandue,
Just modify the first section like this and see if it is working for you ?
for Each vWorksheet in 'Quota 2013','Quota 2014'
QuotasTemp:
CrossTable(QuotaYear, QuotaQuarter, 2)
LOAD
EmployeeID as QuotaEmployeeID,
//SalesPerson,
//'2013' as QuotaYear,
SubField('$(vWorksheet)',' ',2) as QuotaYear_New,
Q1,
Q2,
Q3,
Q4
FROM [$(vPathExcel)Quota.xls]
(biff, embedded labels, header is 1 lines, table is [$(vWorksheet)$]);
Next
You are using field QuotaYear filed twice in the same table 🙂
Please close the thread by marking the correct answer, if you have found the solution 🙂
Thanks to you, I solved it. Thank you.