Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
maxgro
MVP
MVP

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
Luminary Alumni
Luminary Alumni

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
Author

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

Anonymous
Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Hi,

You missed


group by log_opportunityid  in your statement.

Regards,

Jagan.

Not applicable
Author

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

Kind regards

Espen