Re: Error : Field Names must be unique within tabl... - Qlik Community

parul_mehta · ‎2017-06-20

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);

sunny_talwar · ‎2017-06-20

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];

parul_mehta · ‎2017-06-20

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?

sunny_talwar · ‎2017-06-20

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];

parul_mehta · ‎2017-06-20

This value does not show up

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

sunny_talwar · ‎2017-06-20

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];

parul_mehta · ‎2017-06-20

Yes at the bottom

sunny_talwar · ‎2017-06-20

Can you share your log file?

parul_mehta · ‎2017-06-20

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];

sunny_talwar · ‎2017-06-20

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...

Error : Field Names must be unique within table