topic Error : Field Names must be unique within table in QlikView

Error : Field Names must be unique within table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Error : Field Names must be unique within table

Anil_Babu_Samineni — Tue, 20 Nov 2018 07:30:33 GMT

Instead of Load * - Use All field names like Load Field, Field1, ....

Re: Error : Field Names must be unique within table

jensmunnichs — Tue, 20 Nov 2018 07:46:06 GMT

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.

Re: Error : Field Names must be unique within table

jyothish8807 — Tue, 20 Nov 2018 07:54:11 GMT

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

Re: Error : Field Names must be unique within table

jyothish8807 — Tue, 20 Nov 2018 07:55:50 GMT

You are using field QuotaYear filed twice in the same table 🙂

Re: Error : Field Names must be unique within table

jyothish8807 — Tue, 20 Nov 2018 08:51:49 GMT

Please close the thread by marking the correct answer, if you have found the solution 🙂

Re: Error : Field Names must be unique within table

Anonymous — Tue, 20 Nov 2018 08:57:14 GMT

Thanks to you, I solved it. Thank you.