topic Re: Resident load where clause in QlikView

topic Re: Resident load where clause in QlikView https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670090#M243519 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P> When u use arithmetic function in backend script add group by clause</P></BODY></HTML> Thu, 28 Aug 2014 11:27:12 GMT 2014-08-28T11:27:12Z Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670082#M243511 <HTML><HEAD></HEAD><BODY><P>Can anyone tell me why this where caluse on the table minMax_Test gives me an error?</P><P></P><P>Comps:</P><P>LOAD</P><P>  num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %Comp_CalendarKey,</P><P>  date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY') as Comp_BuyDate,</P><P>  Year(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Year,</P><P>  Weekstart(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Week,</P><P>  MonthName(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_MonthName,</P><P>  Month(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_Month,</P><P>  WeekDay(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_DOW,</P><P>     Capitalize(UPPER(replace(F2, 'Outdoor', 'POSTER'))) as Comp_MediaName,</P><P>     DayNumberOfYear(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_DNOY,</P><P>     If(DayNumberOfYear(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,</P><P>     F3 as Comp_Region,</P><P>     F4 as Comp_Channel,</P><P>     //UPPER(replace(F2, 'Outdoor', 'POSTER'))&'_'&Competitor&'_'&num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %compKey,</P><P>     Competitor as Comp_Competitor,</P><P>     MediaSpend as Comp_MediaSpend</P><P>FROM</P><P>  $(ExtractPath)\Comps.qvd(qvd)</P><P>WHERE</P><P>  F1 <> 'Date(Day)' AND</P><P>  F2 <> 'Total' AND</P><P>  Competitor <> 'Grand Total';</P><P></P><P>STORE Comps INTO $(TransformPath)\Comps.qvd(qvd);</P><P>//Drop Table Comps;</P><P>else</P><P>end if</P><P></P><P></P><P></P><P></P><P>minMax_Test:</P><P>load</P><P>  max(Comp_BuyDate) as maxDateComps,</P><P>  Comp_Competitor as Comp_Test</P><P>resident Comps</P><P>where Comp_Competitor = 'clientName';</P></BODY></HTML> Thu, 28 Aug 2014 10:26:42 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670082#M243511 wallerjc 2014-08-28T10:26:42Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670083#M243512 <HTML><HEAD></HEAD><BODY><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">minMax_Test:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">load</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">       max(Comp_BuyDate) as maxDateComps,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">       Comp_Competitor as Comp_Test</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">resident Comps</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">where Comp_Competitor = 'Oka Direct Furnishings'</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Group By <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;">Comp_Competitor</SPAN> ;</P></BODY></HTML> Thu, 28 Aug 2014 10:29:21 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670083#M243512 MK_QSL 2014-08-28T10:29:21Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670084#M243513 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Try below</P><P></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Comps:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">LOAD</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %Comp_CalendarKey,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY') as Comp_BuyDate,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Year(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Year,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Weekstart(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_Week,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  MonthName(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_MonthName,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Month(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_Month,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  WeekDay(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) as Comp_DOW,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     Capitalize(UPPER(replace(F2, 'Outdoor', 'POSTER'))) as Comp_MediaName,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     DayNumberOfYear(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as Comp_DNOY,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     If(DayNumberOfYear(num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY'))) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     F3 as Comp_Region,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     F4 as Comp_Channel,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     //UPPER(replace(F2, 'Outdoor', 'POSTER'))&'_'&Competitor&'_'&num(date(date#(F1, 'DD MMM YYYY'), 'DD/MM/YYYY')) as %compKey,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     Competitor as Comp_Competitor,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">     MediaSpend as Comp_MediaSpend</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">FROM</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  $(ExtractPath)\Comps.qvd(qvd)</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">WHERE</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  F1 <> 'Date(Day)' AND</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  F2 <> 'Total' AND</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Competitor <> 'Grand Total';</P><P></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><SPAN class="jrpw0vx6g" style="font-weight: bold; font-style: inherit; font-family: inherit; margin: 0 !important; padding: 0 !important; border-bottom-width: 1px !important; text-decoration: underline !important; color: #009900 !important;">STORE</SPAN> Comps INTO $(TransformPath)\Comps.qvd(qvd);</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">//<SPAN class="jrpw0vx6g" style="font-weight: bold; font-style: inherit; font-family: inherit; margin: 0 !important; padding: 0 !important; border-bottom-width: 1px !important; text-decoration: underline !important; color: #009900 !important;">Drop Table</SPAN> Comps;</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">else</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">end if</P><P></P><P></P><P></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">minMax_Test:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">load</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  max(Comp_BuyDate) as maxDateComps,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Comp_Competitor as Comp_Test</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">resident Comps</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">where Comp_Competitor = 'clientName'</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>group by <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;">Comp_Competitor </SPAN>;</STRONG></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Regards</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">ASHFAQ</P></BODY></HTML> Thu, 28 Aug 2014 10:30:23 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670084#M243513 ashfaq_haseeb 2014-08-28T10:30:23Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670085#M243514 <HTML><HEAD></HEAD><BODY><P>Hi Manish thanks for your quick response.</P><P></P><P>Could you edit out the COmpetitor name? I left it in by accident</P></BODY></HTML> Thu, 28 Aug 2014 10:35:59 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670085#M243514 wallerjc 2014-08-28T10:35:59Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670086#M243515 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Remember Min and Max  both function are aggregate function</P><P>So when you want to implement Min()/Max() in your script then</P><P>You must have to use Group By clause,</P><P>in where Clause.</P><P></P><P>One more thing you have to include all those non aggregated field in Group By Clause.</P><P></P><P>For detail you can find min in Help Menu.</P><P></P><P>Regards</P></BODY></HTML> Thu, 28 Aug 2014 10:39:06 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670086#M243515 PrashantSangle 2014-08-28T10:39:06Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670087#M243516 <HTML><HEAD></HEAD><BODY><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">minMax_Test:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">load</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  max(Comp_BuyDate) as maxDateComps,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  Comp_Competitor as Comp_Test</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">resident Comps</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">where Comp_Competitor = 'clientName</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;">Group By </SPAN><SPAN style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Comp_Competitor</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;"> ;</SPAN>';</P></BODY></HTML> Thu, 28 Aug 2014 10:54:31 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670087#M243516 MK_QSL 2014-08-28T10:54:31Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670088#M243517 <HTML><HEAD></HEAD><BODY><P>Hi</P><P></P><P>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.</P><P></P><P>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:</P><P></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">minMax_Test:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">load</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  max(Comp_BuyDate) as maxDateComps</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><SPAN style="font-size: 10pt; line-height: 1.5em;">resident Comps</SPAN></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">where Comp_Competitor = 'clientName';</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">HTH</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Jonathan</P></BODY></HTML> Thu, 28 Aug 2014 11:17:48 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670088#M243517 jonathandienst 2014-08-28T11:17:48Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670089#M243518 <HTML><HEAD></HEAD><BODY><P>Hi just to add to <A href="https://community.qlik.com/qlik-users/13229">Jonathan Dienst</A> reply.</P><P></P><P>If you don't want to group field then you can use as below</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">minMax_Test:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">load</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  max(Comp_BuyDate) as maxDateComps,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">  only(Comp_Competitor) as Comp_Test</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">resident Comps</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">where Comp_Competitor = 'clientName';</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Regards</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">ASHFAQ</P></BODY></HTML> Thu, 28 Aug 2014 11:22:04 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670089#M243518 ashfaq_haseeb 2014-08-28T11:22:04Z Re: Resident load where clause https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670090#M243519 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P> When u use arithmetic function in backend script add group by clause</P></BODY></HTML> Thu, 28 Aug 2014 11:27:12 GMT https://community.qlik.com/t5/QlikView/Resident-load-where-clause/m-p/670090#M243519 2014-08-28T11:27:12Z