Converting a SQL code to something useful in Qlikview script

[#1]

Hi

I have a code that pull data from SQL, and I need to convert it to something that will work in qlikview script. Here is the SQL code:

M18 as
(
select distinct log_opportunityid,
createdon = max(createdon),
month = dateadd(m,datediff(m,0,dateadd(m,-18,getdate())),0)
from [dbo].[Filteredlog_salesstagetracking] as t1
where createdon < dateadd(m,datediff(m,0,dateadd(m,-17,getdate())),0)
group by log_opportunityid
),

And this is what I have managed so far, but there's some stuff missing, like the mac(t1.createdon) stuff.....

M18:
LOAD
  log_opportunityid,
// Max(createdon) as createdon,   //Error when this code is active...
  Num(MonthStart(addmonths(today(),-18))) as Month
FROM [..............Filteredlog.qvd] (qvd)
Where createdon < MonthStart(Addmonths(today(),-17));

Kind regards

Espen

[maxgro]

with max (and other aggregation) you need to group by (as in SQL)

I also added a Max to MonthStart

M18: 

LOAD 

     log_opportunityid, 

     Max(createdon) as createdon,   //Error when this code is active... 

     Max(Num(MonthStart(addmonths(today(),-18)))) as Month 

FROM [..............Filteredlog.qvd] (qvd) 

Where createdon < MonthStart(Addmonths(today(),-17))

group by log_opportunityid; 

[jagan · Accepted solution]

Hi,

Try like this

LOAD

*,

MonthStart(Today(), -18)  AS Month;

LOAD 

log_opportunityid, 

max(createdon) AS CreatedOn

from QVDFileName.qvd

where createdon < MonthStart(Today(), -17) 

group by log_opportunityid ;

Hope this helps you.

Regards,

Jagan.

[#4]

Hi Jagan

It seems to be very close to working. But I run this in a loop where I go from 24 months back in time and up til today. And it seems like the code errors after six months in the loop, i wonder if it has to do with the createdon date not matching the criterias in the loop, but i'm not sure.

I have attached a picture from the debug:

Kind regards

Espen

[Anonymous]

Hi,

you are missing a group by statement which is required for aggregation depending on other fields. in your case you need: where createdon < MonthStart(Today(), -17) group by log_opportunityid ;

just as Jagan posted it.

Best

Stefan

[jagan]

Hi,

You missed

group by log_opportunityid  in your statement.

Regards,

Jagan.

[#7]

Thanks, i didn't noticie that i had forgotten to group them.

Kind regards

Espen