topic Lead function in QlikView

Lead function

Tue, 22 Feb 2011 12:04:33 GMT

oracle Lead function is not working in the Edit Script section. I am using Qlikview 9.00 SRS 6.0 version. Please suggest any body has tried this.

Lead function

rbecher — Tue, 22 Feb 2011 12:26:36 GMT

It works fine. I tried on v9 SR6 via ODBC. Maybe your SELECT is wrong or it's an OLE problem.

Could you post a script excerpt?

- Ralf

Lead function

Tue, 22 Feb 2011 12:36:42 GMT

Hi Ralf,

I am using the below Query. Please correct me if i am wrong

SQL SELECT
"ID",
(TO_CHAR((TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((SDATE)/(60*60*24))))) As SDATETIME,
Lead((TO_CHAR((TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((SDATE)/(60*60*24)))))) OVER (ORDER BY (TO_CHAR((TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((SDATE)/(60*60*24)))))) as Newdate
FROM TABLEA
WHERE ID=1 AND SUS =5 AND (TO_CHAR((TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((MDATE)/(60*60*24)))))>=(TO_DATE('01/01/2011 00:00:00','MM/DD/YYYY HH24:MI:SS'));

AW:Re: Lead function

brenner_martina — Tue, 22 Feb 2011 12:38:44 GMT

Hi,

QlikView works like this:

Load Fieldname, ..,
Function(fieldname) As fieldname2;
SQL Select *
from Table
Where ...;

that means you have to enter the function into the Load-statement not into the SQL Select!

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 12:46:00 GMT

Martina,

LEAD is an Oracle function...

- Ralf

AW:Re: Lead function

Tue, 22 Feb 2011 12:46:45 GMT

Hi Ralf,

Can u please help me on this.

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 12:53:13 GMT

Sure, I would do it in a different way, inside out:

LEAD(SDATE) OVER(ORDER BY SDATE)

..and make the calculation outside.

- Ralf

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 13:03:20 GMT

Btw, the LEAD part of your statement is working on my site in QV 9.

So, did you try the query against the database?

- Ralf

AW:Re: Lead function

Tue, 22 Feb 2011 13:09:51 GMT

Hi Ralf,

I have used the way you had given in the previous Query.It is just the conversion within the date. I am getting missing parenthesis error. I am also using the Qlikview 9.00 SRS 6.00 version. Can u post the complete sql statement of my Sql Query.

Thanks for your response.

AW:Re: Lead function

Tue, 22 Feb 2011 13:13:41 GMT

Yes i did except lead function the Query is running Fine without error. Is there any equivalent function for lead in Qlikview

AW:Re: Lead function

Tue, 22 Feb 2011 13:26:35 GMT

Hi Ralf

I am getting the error as Right parenthesis missing.but there is some other error.

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 13:42:01 GMT

Maybe it's the line break? I see no paranthesis is missing.

- Ralf

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 13:42:52 GMT

I don't have your table and data but I wonder if the column MDATE in the Where Clause should be SDATE?

- Ralf

AW:Re: Lead function

rbecher — Tue, 22 Feb 2011 13:52:07 GMT

Ok, you could do this in QlikView in the following way:

1. SELECT the data without the LEAD function but with DESCENDING ordering: ORDER BY SDATE DESC

2.Use the QV function previous( ) on the LOAD statement:

TableA:
LOAD *, previous(SDATETIME) as Newdate;
SQL SELECT .... FROM TABLEA ORDER BY SDATE DESC;

- Ralf

AW:Re: Lead function

Tue, 22 Feb 2011 13:52:12 GMT

Thanks for Support Ralf.

It is MDATE only in the where condition. Hard thing is I will not be able to share the data. But just want to know is there any similar function available in Qlikview.

AW:Re: Lead function

Wed, 23 Feb 2011 07:27:31 GMT

Hi Ralf,

The same Query is working fine if i am executing in SQL plus. Any Suggestions or Alternative approach for this???

AW:Re: Lead function

Wed, 23 Feb 2011 08:13:35 GMT

Hope QlikTech Support team can provide the support for this.

AW:Re: Lead function

rbecher — Wed, 23 Feb 2011 09:50:47 GMT

As I wrote before an alternative could be this:

1. SELECT the data without the LEAD function but with DESCENDING ordering: ORDER BY SDATE DESC

2.Use the QV function previous( ) on the LOAD statement:

TableA:

LOAD *, previous(SDATETIME) as Newdate;

SQL SELECT .... FROM TABLEA ORDER BY SDATE DESC;

- Ralf

AW:Re: Lead function

rbecher — Wed, 23 Feb 2011 09:59:05 GMT

Are you using ODBC or OLE?

Which OS version and which Oracle version you have?

- Ralf

AW:Re: Lead function

Wed, 23 Feb 2011 10:24:53 GMT

Hi ,

I am using OLEDB Connection and the OS version is Windows 2003. Oracle 10g.