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,

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

[nirav_bhimani]

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

[#4]

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

[nirav_bhimani]

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

[#6]

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

[nirav_bhimani]

Hi,

See the attachment its working now.

Regards,

Nirav Bhimani

[#8]

Hi,

We are using personal edition hence cannot open any attachments.

[nirav_bhimani]

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

[#10]

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