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

Error : Field Names must be unique within table

parul_mehta — 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

Fcst:

load *,

Material,

Loc,

[Bus Region],

sum([Demand Forecast $]) as [12 Month Forecast $]

////sum([Demand Forecast QTY]) as [12 Month Forecast QTY],

////sum([6 Month Demand Forecast $]) as [6 Month Forecast $],

////sum([6 Month Demand Forecast QTY]) as [6 Month Forecast QTY],

////sum([Demand Forecast Hist $]) as [12 Month Forecast Hist $],

////sum([Demand Forecast Hist QTY]) as [12 Month Forecast Hist QTY],

////sum([Demand Ship Hist $]) as [12 Month Ship Hist $],

////sum([Demand Ship Hist QTY]) as [12 Month Ship Hist QTY]

Group by

Material,

Loc,

[Bus Region];

Load *,

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext12Months)), [Demand GSV], 0) as [Demand Forecast $],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext12Months)), [Demand QTY], 0) as [Demand Forecast QTY],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext6Months)), [Demand GSV], 0) as [6 Month Demand Forecast $],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext6Months)), [Demand QTY], 0) as [6 Month Demand Forecast QTY],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Demand GSV], 0) as [Demand Forecast Hist $],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Demand QTY], 0) as [Demand Forecast Hist QTY],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Ship GSV], 0) as [Demand Ship Hist $],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Ship QTY], 0) as [Demand Ship Hist QTY]

Where (YearMonth >= $(vLast12Months) and YearMonth <= $(vNext12Months));

LOAD u2.dmd_unit as Material,

u2.dmd_grp as Dmdgroup,

u2.fiscalyear as Year,

u2.fiscal_char_month as Month,

MonthNum,

YearMonth,

u2.loc as Loc,

u2.u_bus_region as [Bus Region],

u2.u_dg_rollup as [Dmdgroup RollUp],

u2.u_gpp_sbu as [GPP SBU],

u2.u_gpp_division as [GPP Div],

u2.u_gpp_category as [GPP Cat],

u2.u_glb_source_of_supply_agg as [Sos of supply agg],

u2.u_gpp_portfolio_grp as [GPP Port],

u2.u_glb_preferred_vendor as [Preferred Vendor],

// u2.u_division_c11,

u2.descr,

// u2.gpp_category_description,

// u2.portfolio_description,

// u2.preferred_vendor_desc,

// u2.source_of_supply_desc,

u2.planned_fcst_qty as [Demand QTY],

u2.planned_fcst_gsv as [Demand GSV],

u2.actual_fcst_qty as [Ship QTY],

u2.actual_fcst_gsv as [Ship GSV]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 14:32:24 GMT

I think your problem is right here

You are doing a LOAD *, which brings Material, Loc and Bus Region and you are again specifying them again. May be you need this

Fcst:

LOAD Material,

Loc,

[Bus Region],

sum([Demand Forecast $]) as [12 Month Forecast $]

Group by Material, Loc, [Bus Region];

Re: Error : Field Names must be unique within table

parul_mehta — Tue, 20 Jun 2017 14:40:00 GMT

Removed the *

but in that case only fields LOC , BUS Region , MAterial , 12month Forecst $ are coming in listboxes

The rest are not available

What needs to be done in this case?

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 14:44:14 GMT

May be this

Fcst:

LOAD *,

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext12Months)), [Demand GSV], 0) as [Demand Forecast $],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext12Months)), [Demand QTY], 0) as [Demand Forecast QTY],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext6Months)), [Demand GSV], 0) as [6 Month Demand Forecast $],

if((YearMonth >= $(vCurYearMonth)) and (YearMonth <= $(vNext6Months)), [Demand QTY], 0) as [6 Month Demand Forecast QTY],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Demand GSV], 0) as [Demand Forecast Hist $],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Demand QTY], 0) as [Demand Forecast Hist QTY],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Ship GSV], 0) as [Demand Ship Hist $],

if((YearMonth >= $(vLast12Months)) and (YearMonth < $(vCurYearMonth)), [Ship QTY], 0) as [Demand Ship Hist QTY]

Where (YearMonth >= $(vLast12Months) and YearMonth <= $(vNext12Months));

LOAD u2.dmd_unit as Material,

u2.dmd_grp as Dmdgroup,

u2.fiscalyear as Year,

u2.fiscal_char_month as Month,

MonthNum,

YearMonth,

u2.loc as Loc,

u2.u_bus_region as [Bus Region],

u2.u_dg_rollup as [Dmdgroup RollUp],

u2.u_gpp_sbu as [GPP SBU],

u2.u_gpp_division as [GPP Div],

u2.u_gpp_category as [GPP Cat],

u2.u_glb_source_of_supply_agg as [Sos of supply agg],

u2.u_gpp_portfolio_grp as [GPP Port],

u2.u_glb_preferred_vendor as [Preferred Vendor],

u2.descr,

u2.planned_fcst_qty as [Demand QTY],

u2.planned_fcst_gsv as [Demand GSV],

u2.actual_fcst_qty as [Ship QTY],

u2.actual_fcst_gsv as [Ship GSV]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left Join (Fcst)

LOAD Material,

Loc,

[Bus Region],

sum([Demand Forecast $]) as [12 Month Forecast $]

Group by Material, Loc, [Bus Region];

Re: Error : Field Names must be unique within table

parul_mehta — Tue, 20 Jun 2017 14:53:57 GMT

This value does not show up

sum([Demand Forecast $]) as [12 Month Forecast $]

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 14:55:07 GMT

Did you do a left join at the bottom?

Left Join (Fcst)

LOAD Material,

Loc,

[Bus Region],

sum([Demand Forecast $]) as [12 Month Forecast $]

Group by Material, Loc, [Bus Region];

Re: Error : Field Names must be unique within table

parul_mehta — Tue, 20 Jun 2017 14:57:28 GMT

Yes at the bottom

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 15:01:53 GMT

Can you share your log file?

Re: Error : Field Names must be unique within table

parul_mehta — Tue, 20 Jun 2017 15:03:29 GMT

If I do as below and add alias for material , loc, busregion , I get the error " Invalid Expression ", even though I have used all three fields in group by clause

Fcst:

load *,

Material as M1,

Loc as L1,

[Bus Region] as B1,

sum([Demand Forecast $]) as [12 Month Forecast $]

Group by

Material,

Loc,

[Bus Region];

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 15:05:48 GMT

Your group by needs to include all the fields you are aggregating on... and those include all the non-aggregating field in your table load.... I am not sure what your final goal is...

Re: Error : Field Names must be unique within table

parul_mehta — Tue, 20 Jun 2017 15:13:47 GMT

Need the sum of ([Demand Forecast $]) based on

Material,

Loc,

[Bus Region];

but still getting error invalid expression

Re: Error : Field Names must be unique within table

sunny_talwar — Tue, 20 Jun 2017 15:15:13 GMT

This should work Parul....