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
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);
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];
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?
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];
This value does not show up
sum([Demand Forecast $]) as [12 Month Forecast $]
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];
Yes at the bottom
Can you share your log file?
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];
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...