Resident load where clause

[wallerjc]

Can anyone tell me why this where caluse on the table minMax_Test gives me an error?

Comps:

LOAD

  num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %Comp_CalendarKey,

  date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY') as Comp_BuyDate,

  Year(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Year,

  Weekstart(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Week,

  MonthName(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_MonthName,

  Month(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_Month,

  WeekDay(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_DOW,

     Capitalize(UPPER(replace(F2, 'Outdoor', 'POSTER'))) as Comp_MediaName,

     DayNumberOfYear(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_DNOY,

     If(DayNumberOfYear(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,

     F3 as Comp_Region,

     F4 as Comp_Channel,

     //UPPER(replace(F2, 'Outdoor', 'POSTER'))&'_'&Competitor&'_'&num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %compKey,

     Competitor as Comp_Competitor,

     MediaSpend as Comp_MediaSpend

FROM

  $(ExtractPath)\Comps.qvd(qvd)

WHERE

  F1 <> 'Date(Day)' AND

  F2 <> 'Total' AND

  Competitor <> 'Grand Total';

STORE Comps INTO $(TransformPath)\Comps.qvd(qvd);

//Drop Table Comps;

else

end if

minMax_Test:

load

  max(Comp_BuyDate) as maxDateComps,

  Comp_Competitor as Comp_Test

resident Comps

where Comp_Competitor = 'clientName';

[MK_QSL · Accepted solution]

minMax_Test:

load

       max(Comp_BuyDate) as maxDateComps,

       Comp_Competitor as Comp_Test

resident Comps

where Comp_Competitor = 'Oka Direct Furnishings'

Group By Comp_Competitor ;

[ashfaq_haseeb]

Hi,

Try below

Comps:

LOAD

  num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %Comp_CalendarKey,

  date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY') as Comp_BuyDate,

  Year(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Year,

  Weekstart(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Week,

  MonthName(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_MonthName,

  Month(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_Month,

  WeekDay(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_DOW,

     Capitalize(UPPER(replace(F2, 'Outdoor', 'POSTER'))) as Comp_MediaName,

     DayNumberOfYear(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_DNOY,

     If(DayNumberOfYear(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,

     F3 as Comp_Region,

     F4 as Comp_Channel,

     //UPPER(replace(F2, 'Outdoor', 'POSTER'))&'_'&Competitor&'_'&num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %compKey,

     Competitor as Comp_Competitor,

     MediaSpend as Comp_MediaSpend

FROM

  $(ExtractPath)\Comps.qvd(qvd)

WHERE

  F1 <> 'Date(Day)' AND

  F2 <> 'Total' AND

  Competitor <> 'Grand Total';

STORE Comps INTO $(TransformPath)\Comps.qvd(qvd);

//Drop Table Comps;

else

end if

minMax_Test:

load

  max(Comp_BuyDate) as maxDateComps,

  Comp_Competitor as Comp_Test

resident Comps

where Comp_Competitor = 'clientName'

group by Comp_Competitor ;

Regards

ASHFAQ

[wallerjc]

Hi Manish thanks for your quick response.

Could you edit out the COmpetitor name? I left it in by accident

[PrashantSangle]

Hi,

Remember Min and Max  both function are aggregate function

So when you want to implement Min()/Max() in your script then

You must have to use Group By clause,

in where Clause.

One more thing you have to include all those non aggregated field in Group By Clause.

For detail you can find min in Help Menu.

Regards

[MK_QSL]

minMax_Test:

load

  max(Comp_BuyDate) as maxDateComps,

  Comp_Competitor as Comp_Test

resident Comps

where Comp_Competitor = 'clientName

Group By Comp_Competitor ;';

[jonathandienst]

Hi

When using an aggregation function in a LOAD, all non-aggregated fields returned by the function need to be listed in a group by clause. I suspect that you added the Comp_Competitor for testing and that broke the query.

So if you include Comp_Competitor in the result set, you need the Group By. If you take that field out of the result set, you won;t need a Group By. This does not apply to the Where clause, so you could have:

minMax_Test:

load

  max(Comp_BuyDate) as maxDateComps

resident Comps

where Comp_Competitor = 'clientName';

HTH

Jonathan

[ashfaq_haseeb]

Hi just to add to Jonathan Dienst reply.

If you don't want to group field then you can use as below

minMax_Test:

load

  max(Comp_BuyDate) as maxDateComps,

  only(Comp_Competitor) as Comp_Test

resident Comps

where Comp_Competitor = 'clientName';

Regards

ASHFAQ

[#9]

Hi,

When u use arithmetic function in backend script add group by clause