Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Converting a SQL code to something useful in Qlikview script

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

Tags (4)
1 Solution

Accepted Solutions
jagan
Not applicable

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

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.

6 Replies
maxgro
Not applicable

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

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
Not applicable

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

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.

Not applicable

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

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:error loop.jpg

Kind regards

Espen

s_kunte23
Not applicable

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

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
Not applicable

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

Hi,

You missed


group by log_opportunityid  in your statement.

Regards,

Jagan.

Not applicable

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

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

Kind regards

Espen