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);
Need the sum of ([Demand Forecast $]) based on
Material,
Loc,
[Bus Region];
but still getting error invalid expression
This should work Parul....
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];
6/20/2017 8:37:05 PM: Execution started.
6/20/2017 8:37:05 PM: QlikView Version:11.20.13206.0
6/20/2017 8:37:05 PM: CPU Target x64
6/20/2017 8:37:05 PM: Operating System Windows 7 Professional Service Pack 1 (64 bit edition)
6/20/2017 8:37:05 PM: Wow64 mode Not using Wow64
6/20/2017 8:37:05 PM: MDAC Version 6.1.7601.17514
6/20/2017 8:37:05 PM: MDAC Full Install Version 6.1.7601.17514
6/20/2017 8:37:05 PM: PreferredCompression 2
6/20/2017 8:37:05 PM: EnableParallelReload 1
6/20/2017 8:37:05 PM: ParallelizeQvdLoads 1
6/20/2017 8:37:05 PM: AutoSaveAfterReload 0
6/20/2017 8:37:05 PM: BackupBeforeReload 0
6/20/2017 8:37:05 PM: EnableFlushLog 0
6/20/2017 8:37:05 PM: SaveInfoWhenSavingFile 0
6/20/2017 8:37:05 PM: UserLogfileCharset 1200
6/20/2017 8:37:05 PM: OdbcLoginTimeout -1
6/20/2017 8:37:05 PM: OdbcConnectionTimeout -1
6/20/2017 8:37:05 PM: ScriptWantsDbWrite false
6/20/2017 8:37:05 PM: ScriptWantsExe false
6/20/2017 8:37:05 PM: LogFile CodePage Used: 1200
6/20/2017 8:37:05 PM: Reload Executed By AMERICAS\RXS0620A
6/20/2017 8:37:05 PM: Process Executing: QlikView Desktop
6/20/2017 8:37:05 PM: Process ID: 4592
6/20/2017 8:37:05 PM: 0002 SET ThousandSep=','
6/20/2017 8:37:05 PM: 0003 SET DecimalSep='.'
6/20/2017 8:37:05 PM: 0004 SET MoneyThousandSep=','
6/20/2017 8:37:05 PM: 0005 SET MoneyDecimalSep='.'
6/20/2017 8:37:05 PM: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'
6/20/2017 8:37:05 PM: 0007 SET TimeFormat='h:mm:ss TT'
6/20/2017 8:37:05 PM: 0008 SET DateFormat='M/D/YYYY'
6/20/2017 8:37:05 PM: 0009 SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'
6/20/2017 8:37:05 PM: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
6/20/2017 8:37:05 PM: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
6/20/2017 8:37:05 PM: 0015 SET vForecastPath='[\\tow-t-qlikshare\Groups\QV_DATA_QVD\XFM_GTS\DTV\Forecast\]'
6/20/2017 8:37:05 PM: 0016 SET vInvPath='[\\tow-p-qlikshare\Groups\QV_DATA_QVD\XFM_GTS\DTV\Complexity Management Dashboard\Inventory - POC\]'
6/20/2017 8:37:05 PM: 0018 SET vSharedDrive='[\\americas.swk.pri\Groups\North_America\Towson-TOW\DTV\Complexity Management\DA_Infosys\LPS Dashboard\]'
6/20/2017 8:37:05 PM: 0022 vToday=date(Date#(Today()),'YYYYMMDD')
6/20/2017 8:37:05 PM: 0023 vYearWeek ='0'&mid(Replace(WeekName(Today(),1),'/',''),5,2) & mid(Replace(WeekName(Today(),1),'/',''),1,4)
6/20/2017 8:37:05 PM: 0026 Let vCurYearMonth = Date(Today(),'YYYYMM')
6/20/2017 8:37:05 PM: 0027 Let vPrevYearMonth= Date(num(MonthStart(Today(),-1)),'YYYYMM')
6/20/2017 8:37:05 PM: 0028 Let vLast12Months= Date(num(MonthStart(Today(),-12)),'YYYYMM')
6/20/2017 8:37:05 PM: 0029 Let vNext12Months= Date(num(MonthStart(Today(),11)),'YYYYMM')
6/20/2017 8:37:05 PM: 0030 LET vNext6Months= Date(num(MonthStart(Today(),5)),'YYYYMM')
6/20/2017 8:37:05 PM: 0249 Fcst:
6/20/2017 8:37:05 PM: 0250
6/20/2017 8:37:05 PM: 0251 Load *,
6/20/2017 8:37:05 PM: 0252 if((YearMonth >= 201706) and (YearMonth <= 201805), [Demand GSV], 0) as [Demand Forecast $],
6/20/2017 8:37:05 PM: 0253 if((YearMonth >= 201706) and (YearMonth <= 201805), [Demand QTY], 0) as [Demand Forecast QTY],
6/20/2017 8:37:05 PM: 0254 if((YearMonth >= 201706) and (YearMonth <= 201711), [Demand GSV], 0) as [6 Month Demand Forecast $],
6/20/2017 8:37:05 PM: 0255 if((YearMonth >= 201706) and (YearMonth <= 201711), [Demand QTY], 0) as [6 Month Demand Forecast QTY],
6/20/2017 8:37:05 PM: 0256 if((YearMonth >= 201606) and (YearMonth < 201706), [Demand GSV], 0) as [Demand Forecast Hist $],
6/20/2017 8:37:05 PM: 0257 if((YearMonth >= 201606) and (YearMonth < 201706), [Demand QTY], 0) as [Demand Forecast Hist QTY],
6/20/2017 8:37:05 PM: 0258 if((YearMonth >= 201606) and (YearMonth < 201706), [Ship GSV], 0) as [Demand Ship Hist $],
6/20/2017 8:37:05 PM: 0259 if((YearMonth >= 201606) and (YearMonth < 201706), [Ship QTY], 0) as [Demand Ship Hist QTY]
6/20/2017 8:37:05 PM: 0260 Where (YearMonth >= 201606 and YearMonth <= 201805)
6/20/2017 8:37:05 PM: 0262 LOAD u2.dmd_unit as Material,
6/20/2017 8:37:05 PM: 0263 u2.dmd_grp as Dmdgroup,
6/20/2017 8:37:05 PM: 0264 u2.fiscalyear as Year,
6/20/2017 8:37:05 PM: 0265 u2.fiscal_char_month as Month,
6/20/2017 8:37:05 PM: 0266 MonthNum,
6/20/2017 8:37:05 PM: 0267 YearMonth,
6/20/2017 8:37:05 PM: 0268 u2.loc as Loc,
6/20/2017 8:37:05 PM: 0269 u2.u_bus_region as [Bus Region],
6/20/2017 8:37:05 PM: 0270 u2.u_dg_rollup as [Dmdgroup RollUp],
6/20/2017 8:37:05 PM: 0271 u2.u_gpp_sbu as [GPP SBU],
6/20/2017 8:37:05 PM: 0272 u2.u_gpp_division as [GPP Div],
6/20/2017 8:37:05 PM: 0273 u2.u_gpp_category as [GPP Cat],
6/20/2017 8:37:05 PM: 0274 u2.u_glb_source_of_supply_agg as [Sos of supply agg],
6/20/2017 8:37:05 PM: 0275 u2.u_gpp_portfolio_grp as [GPP Port],
6/20/2017 8:37:05 PM: 0276 u2.u_glb_preferred_vendor as [Preferred Vendor],
6/20/2017 8:37:05 PM: 0277
6/20/2017 8:37:05 PM: 0278 u2.descr,
6/20/2017 8:37:05 PM: 0279
6/20/2017 8:37:05 PM: 0280
6/20/2017 8:37:05 PM: 0281
6/20/2017 8:37:05 PM: 0282
6/20/2017 8:37:05 PM: 0283 u2.planned_fcst_qty as [Demand QTY],
6/20/2017 8:37:05 PM: 0284 u2.planned_fcst_gsv as [Demand GSV],
6/20/2017 8:37:05 PM: 0285 u2.actual_fcst_qty as [Ship QTY],
6/20/2017 8:37:05 PM: 0286 u2.actual_fcst_gsv as [Ship GSV]
6/20/2017 8:37:05 PM: 0287 FROM
6/20/2017 8:37:05 PM: 0288
6/20/2017 8:37:05 PM: 0289 (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
6/20/2017 8:37:05 PM: 28 fields found: Material, Dmdgroup, Year, Month, MonthNum, YearMonth, Loc, Bus Region, Dmdgroup RollUp, GPP SBU, GPP Div, GPP Cat, Sos of supply agg, GPP Port, Preferred Vendor, u2.descr, Demand QTY, Demand GSV, Ship QTY, Ship GSV, Demand Forecast $, Demand Forecast QTY, 6 Month Demand Forecast $, 6 Month Demand Forecast QTY, Demand Forecast Hist $, Demand Forecast Hist QTY, Demand Ship Hist $, Demand Ship Hist QTY, 24 lines fetched
6/20/2017 8:37:05 PM: 0291 Left Join (Fcst)
6/20/2017 8:37:05 PM: 0292 load
6/20/2017 8:37:05 PM: 0293 Material,
6/20/2017 8:37:05 PM: 0294 Loc ,
6/20/2017 8:37:05 PM: 0295 [Bus Region],
6/20/2017 8:37:05 PM: 0296 sum([Demand Forecast $]) as [12 Month Forecast $],
6/20/2017 8:37:05 PM: 0297 sum([Demand Forecast QTY]) as [12 Month Forecast QTY],
6/20/2017 8:37:05 PM: 0298 sum([6 Month Demand Forecast $]) as [6 Month Forecast $],
6/20/2017 8:37:05 PM: 0299 sum([6 Month Demand Forecast QTY]) as [6 Month Forecast QTY],
6/20/2017 8:37:05 PM: 0300 sum([Demand Forecast Hist $]) as [12 Month Forecast Hist $],
6/20/2017 8:37:05 PM: 0301 sum([Demand Forecast Hist QTY]) as [12 Month Forecast Hist QTY],
6/20/2017 8:37:05 PM: 0302 sum([Demand Ship Hist $]) as [12 Month Ship Hist $],
6/20/2017 8:37:05 PM: 0303 sum([Demand Ship Hist QTY]) as [12 Month Ship Hist QTY]
6/20/2017 8:37:05 PM: 0304 Group by
6/20/2017 8:37:05 PM: 0305 Material,
6/20/2017 8:37:05 PM: 0306 Loc,
6/20/2017 8:37:05 PM: 0307 [Bus Region]
6/20/2017 8:37:05 PM: Execution finished.
Do I need to add any other field as well ?
Need the sum of ([Demand Forecast $]) based on
Material,
Loc,
[Bus Region];
but still getting error invalid expression
I see that the join came through... what is the issue? with 12 Month Forecast $?
I have this also, and there are no dup'ed in my query. Why dont you just fix this in Qlik, so that you take unique fields, if you for whatever reason think you see duplicates... This is QLIK Bug.
Field names must be unique within table
LOAD [{"@odata.context":"http://localhost:8080/SERVICE.svc/$metadata#DeskBooks"],
"""value"":[{""Identifier"":607323682",
[Desk":"FundXX],
["Book":"DEF"}],
[{"Identifier":607323742],
[Desk":"FundXX],
["Book":"ABC"}],
[{"Identifier":1294410539],
[Desk":"Gbl ],
"""Book"":""ABC""}]}"
FROM
[http://localhost:8080/serrice.svc/DeskBooks]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
Qlik is buggy ???