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

[#1]

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

[nirav_bhimani]

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

[#3]

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;

[nirav_bhimani]

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

[#5]

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

[#6]

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

Thanks,

Hitesh