Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
parul_mehta
Partner - Creator
Partner - Creator

Error : Field Names must be unique within table

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

 

 

16 Replies
parul_mehta
Partner - Creator
Partner - Creator
Author

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

Material,

  Loc,

  [Bus Region];

but still getting error invalid expression

sunny_talwar

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

parul_mehta
Partner - Creator
Partner - Creator
Author

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.

parul_mehta
Partner - Creator
Partner - Creator
Author

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

sunny_talwar

I see that the join came through... what is the issue? with 12 Month Forecast $?

Capture.PNG

holmpeter
Contributor
Contributor

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.

holmpeter
Contributor
Contributor

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