topic Re: Converting a SQL code to something useful in Qlikview script in QlikView

Converting a SQL code to something useful in Qlikview script

Sun, 26 Oct 2014 20:45:11 GMT

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

Re: Converting a SQL code to something useful in Qlikview script

maxgro — Sun, 26 Oct 2014 21:47:27 GMT

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;

Re: Converting a SQL code to something useful in Qlikview script

jagan — Mon, 27 Oct 2014 04:23:01 GMT

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.

Re: Converting a SQL code to something useful in Qlikview script

Mon, 27 Oct 2014 10:44:34 GMT

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

Re: Converting a SQL code to something useful in Qlikview script

Anonymous — Mon, 27 Oct 2014 20:43:38 GMT

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

Re: Converting a SQL code to something useful in Qlikview script

jagan — Tue, 28 Oct 2014 02:31:07 GMT

Hi,

You missed

group by log_opportunityid in your statement.

Regards,

Jagan.

Re: Converting a SQL code to something useful in Qlikview script

Tue, 28 Oct 2014 08:24:39 GMT

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

Kind regards

Espen