topic Re: Hello, Please anyone help me how to write below SQL query in Qlikview. in QlikView

Hello, Please anyone help me how to write below SQL query in Qlikview.

Thu, 28 Mar 2013 04:40:01 GMT

select potentialid,snapshotdate,closingdate,amount,sales_stage FROM dbo.snapshot_feb28

WHERE DATEDIFF(dd,snapshotdate,closingdate) <=90 order by closingdate

union

select potentialid,cast(GETDATE() as DATE) as todaysdate,closingdate,amount,sales_stage from potential

WHERE DATEDIFF(dd,closingdate,GETDATE()) <= 90

and sales_stage not in ('Closed Won','Closed Lost','Lead Dead')

order by closingdate

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 05:33:48 GMT

Hi,

Table1:

select potentialid,snapshotdate,closingdate,amount,sales_stage

FROM dbo.snapshot_feb28

NOCONCATENATE

Table2:

select potentialid,closingdate,amount,sales_stage

from potential

NOCONCATENATE

FINAL:

LOAD *, Date(TODAY()) as todaysdate

RESIDENT TABLE2

WHERE Interval( Date(closingdate) - Date (Today()) ,'D' ) <=90 and

Not Wildmatch('sales_stage','Closed Won','Closed Lost','Lead Dead')

order by closingdate;

Concatenate

LOAD * RESIDENT TABLE1

WHERE Interval ( Date(snapshotdate) - closingdate, 'D' ) <=90

order by closingdate;

Drop table Table1;

Drop table Table2;

If Interval function doesnot work then make that condition as a fileld and then use same field in resident in where clause.Hope this will help you.

Reagdrs,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 05:51:42 GMT

Hi,

If above code is not working properly then try this script, In this date diff. is calculated as a field.

Table1:

select potentialid,snapshotdate,closingdate,amount,sales_stage

FROM dbo.snapshot_feb28

NOCONCATENATE

Table2:

select potentialid,closingdate,amount,sales_stage

from potential

NOCONCATENATE

TEMP2:

LOAD *, Date (TODAY()) as todaysdate , Interval( Date(closingdate) - Date (Today()) ,'D' ) as DInterval2

RESIDENT TABLE2;

NOCONCATENATE

TEMP1:

LOAD *, Interval ( Date(snapshotdate) - closingdate, 'D' ) <=90 as DInterval1

RESIDENT TABLE1;

Drop table Table1;

Drop table Table2;

NOCONCATENATE

FINAL:

LOAD * RESIDENT TEMP2

WHERE DInterval2 <=90 and

Not Wildmatch('sales_stage','Closed Won','Closed Lost','Lead Dead')

order by closingdate;

CONCATENATE

LOAD * RESIDENT TEMP1

WHERE DInterval1 <=90

order by closingdate;

DROP TABLE TEMP2;

DROP TABLE TEMP1;

Regards,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Thu, 28 Mar 2013 05:59:26 GMT

Do you think that the customized query as mention by you in the above blog will work in Desktop(Personal Edition)?

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 06:14:08 GMT

HI,

If those table are already present in the database then first pull the field which you wanted then perform the operation on resident table.

Reagrds,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Thu, 28 Mar 2013 06:26:40 GMT

I'm getting following error......

Table not found

NOCONCATENATE

TEMP2:

LOAD *, Date (TODAY()) as todaysdate , Interval( Date(closingdate) - Date (Today()) ,'D' ) as DInterval2

RESIDENT TABLE2

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 06:42:01 GMT

Hi,

See the attachment its working now.

Regards,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Thu, 28 Mar 2013 07:05:11 GMT

Hi,

We are using personal edition hence cannot open any attachments.

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 07:09:53 GMT

Hi,

This code is running;

Table1:

load * Inline [

potentialid,snapshotdate,closingdate,amount,sales_stage

1,2/4/2012, 2/3/2012,12,1

];

//FROM dbo.snapshot_feb28

NOCONCATENATE

Table2:

load * INLINE [

potentialid,closingdate,amount,sales_stage

2,2/3/2012,12,1

];

NOCONCATENATE

TEMP1:

LOAD *, Interval ( Date(snapshotdate) - closingdate, 'D' ) <=90 as DInterval1

RESIDENT Table1;

NOCONCATENATE

TEMP2:

LOAD *, Date (TODAY()) as todaysdate , Interval( Date(closingdate) - Date (Today()) ,'D' ) as DInterval2

RESIDENT Table2;

Drop table Table1;

Drop table Table2;

NOCONCATENATE

FINAL:

LOAD * RESIDENT TEMP2

WHERE DInterval2 <=90 and

Not Wildmatch('sales_stage','Closed Won','Closed Lost','Lead Dead')

order by closingdate;

CONCATENATE

LOAD * RESIDENT TEMP1

WHERE DInterval1 <=90

order by closingdate;

DROP TABLE TEMP2;

DROP TABLE TEMP1;

Regards,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Thu, 28 Mar 2013 11:45:01 GMT

Hi Nirav,
thanx your code worked but my output is coming wrong.
I have highlighted on x axis. How to remove those null values.

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Thu, 28 Mar 2013 12:03:18 GMT

Hi,

To avoid null values from that column add the following script in the where clause, in that specify your column name which is showing null values.

where not WildMatch(ColName,' ') or len(ColName) <> 0;

Hope this will resolved your problem.

Regards,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Fri, 29 Mar 2013 05:33:50 GMT

Hi Nirav,

PFA as I cud not remove those null values.

and the code i am using is given below.

reply asap

Thanx,

ODBC CONNECT32 TO MSSQL (XUserId is CAfacZFMfA, XPassword is QdKEGYFMTbcAUYIGTRSB);

Table1:

select potentialid ,snapshotdate ,closingdate ,amount ,sales_stage

FROM Qlikview.dbo.snapshot;

NOCONCATENATE

Table2:

select potentialid,closingdate,amount,sales_stage

from Qlikview.dbo.potential;

NOCONCATENATE

FINAL:

LOAD *, Date(TODAY()) as todaysdate

RESIDENT Table2

WHERE Interval( Date(closingdate) - Date (Today()) ,'D' ) <=90 and

not WildMatch(sales_stage,'Closed Won','Closed Lost','Lead Dead') and

not WildMatch(closingdate,'')

order by closingdate;

Concatenate

LOAD * RESIDENT Table1

WHERE Interval ( Date(snapshotdate) - closingdate, 'D' ) <=90 and

not WildMatch(snapshotdate,'')

order by closingdate;

Drop table Table1;

Drop table Table2;

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

nirav_bhimani — Sat, 30 Mar 2013 09:48:33 GMT

Hi,

Pelase see the attachment, In my case it doesnot show and null values in the date field of chart.

I have use the condition like this.

TEMP1:

LOAD *, Interval ( Date(snapshotdate) - closingdate, 'D' ) <=90 as DInterval1

RESIDENT Table1

where not WildMatch(snapshotdate,' ') and not WildMatch(closingdate,' ') or len(closingdate) <> 0;

NOCONCATENATE

TEMP2:

LOAD *, Date (TODAY()) as todaysdate , Interval( Date(closingdate) - Date (Today()) ,'D' ) as DInterval2

RESIDENT Table2

Where not WildMatch(closingdate,' ') or len(closingdate) <> 0;

Reagrds,

Nirav Bhimani

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Sat, 30 Mar 2013 10:57:55 GMT

Hi,
If i run this code i get two fields Dinterval1, Dinterval2...
but the amount field is showing primary key....were we cant use it in bar chart for expression on x-axis....
and even tell me which field i have to take in bar chart to get that result?

And one more thing ...I cannot open the .qvm docs wch you are attaching so if possible you can send it in other format like copying it in MS word or pdf...

Thanks,
Hitesh

Re: Hello, Please anyone help me how to write below SQL query in Qlikview.

Mon, 01 Apr 2013 05:37:40 GMT

Hi,
I got the soln for primary key...
code is running but same problem of null values...

Thanks,

Hitesh