topic Re: Transform SQL select to QV expression in QlikView

Transform SQL select to QV expression

Thu, 25 Oct 2012 14:22:53 GMT

Hello QV developers,

please, could anybody help me with transforming SQL select to QV expression? I have created an example QVW.

I want to see Orders (work in progress in specified season) as is specified in this MS SQL select (orders in progress till 31.1. 2012).

select COUNT(*) as Order from KPI_Orders

where ((Order_date_income <='20120131 23:59:59' and (Order_date_closed >'20120131 23:59:59' or Order_closed=0))

or (Order_date_income <='20120131 23:59:59' and Order_date_closed ='19000101 00:00:00'))

The correct number of this select in MS SQL is 54.

Thank you for any help!

Mirek

Re: Transform SQL select to QV expression

Gysbert_Wassenaar — Thu, 25 Oct 2012 14:45:08 GMT

Once you cast the date strings to real dates the calculation will show the correct count:

count(DISTINCT(If (Order_date_income <=date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') and (Order_date_closed >date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') or Order_closed='0')

or (Order_date_income <=date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') and Order_date_closed =date#('19000101 00:00:00','YYYYMMDD hh:mm:ss')), Order)))

Re: Transform SQL select to QV expression

Thu, 25 Oct 2012 15:02:55 GMT

Thank you, I found the problem with my date format, is different from date in SQL select. Now it's works and show the correct number 🙂

Now I need to find solution, how to put variables instead dates in expression. To be able specify the date in expression. Should I create "Data Island"?

Re: Transform SQL select to QV expression

Gysbert_Wassenaar — Thu, 25 Oct 2012 16:22:52 GMT

You don't need a data island calendar in this case. First create a new variable, for example vDate. Then add a Calendar object and connect it to a variable vDate. You can then use the Calendar object to select a date and vDate will be set to this value. You can then use vDate in your expression.

Re: Transform SQL select to QV expression

Mon, 29 Oct 2012 10:43:16 GMT

Thank you very much for this elegant solution 🙂