topic Script Help ideas in QlikView

Script Help ideas

ivandrago — Mon, 07 Jun 2010 21:12:34 GMT

Hi,
I have the below query in sql server 2005, how would I create a qvd file for this as not sure how to handle inline views?

select
tablea.appointmentNo,
tablea.son,
tablea.PersonId,
case when app.callno is null then 0 else 1 end as LastCall,
cus.queue

from
tablea

left outer join (select son, max(appointmentNo) as lastAppointmentNo from tableb group by son) app
on tablea.son = app.son
and tablea.appointmentNo = app.lastAppointmentNo

left outer join (select son, queue, FixFromOpen from tablec union select son, queue,FixFromOpen from tabled
where not exists (select * from TableD z where z.callNo = tablec.CallNo)) cus
on tablea.son = cus.son

where
tablea.status='Live'

Thanks

Script Help ideas

ivandrago — Mon, 07 Jun 2010 23:38:23 GMT

Any experts out there? That can tell me if this can be done or not?

Script Help ideas

Mon, 07 Jun 2010 23:42:28 GMT

Hi Ivan,

Perhaps I'm misunderstanding, but if you have the SQL query why don't you do one of these:

1) define it as a view in SQL and pull data from the view

2) inside your LOAD statement, just SQL SELECT the whole query you've just posted here

either of these will load the data from the query you've provided.

Script Help ideas

ivandrago — Tue, 08 Jun 2010 00:04:31 GMT

Hi, I want to avoid creating a view in sql server, how would you do 2) can you provide a starting example?

Thanks

Script Help ideas

Tue, 08 Jun 2010 00:10:51 GMT

Sure. My Semi-coloning might not be totally accurate. I'm writing this on the fly.


[My Table Name]:
LOAD
*;
SQL SELECT
tablea.appointmentNo,
 tablea.son,
 tablea.PersonId,
 case when app.callno is null then 0 else 1 end as LastCall,
 cus.queue

from
 tablea

 left outer join (select son, max(appointmentNo) as lastAppointmentNo from tableb group by son) app
 on tablea.son = app.son
 and tablea.appointmentNo = app.lastAppointmentNo

 left outer join (select son, queue, FixFromOpen from tablec union select son, queue,FixFromOpen from tabled
 where not exists (select * from TableD z where z.callNo = tablec.CallNo)) cus
 on tablea.son = cus.son


where
 tablea.status='Live';

Script Help ideas

ivandrago — Tue, 08 Jun 2010 20:26:13 GMT

Thanks Fry!