Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

parispantelis
New Contributor II

transfer SQL query to Qlikview script

Hi,

I have a query on my SQL server and i would like to transfer it to Qlikview because my tables from now on will be there (one of them is inline) and the other is loaded from file.

I Have these two Tables on SQL:

1. dbo.cases with fields -> id,start,close (these are the case id, the date the case started, the date case closed)

2. dbo.calendar with fields -> date,daytype (calendar date and daytype=1 for working days or 0 for not working days)

My SQL query is:

--------------------------------------------------------------------------------------------------------------

SELECT id,start,close,count(daytype) as dayscount

FROM [dbo].[cases]

inner join [dbo].calendar on (calendar.Date>cases.Start and calendar.Date<=cases.close)

where daytype='1'

group by  start,close,id,daytype

order by cases.start desc

--------------------------------------------------------------------------------------------------------------

i don't know how to write the same qry in Qlikview script and any help whould by very valuable.

PS: these SQL query is not working anymore because this SQL Server does not exist and the same tables are now loaded (inline or from file). So i have the data already loaded in my Qlikview document.

1 Solution

Accepted Solutions
MVP
MVP

Re: transfer SQL query to Qlikview script

Assuming you have resident tables cases and calendar, the using an interval match:

C1:

NoConcatenate

LOAD id,

  start,

  close

Resident cases

T1:

NoConcatenate

LOAD date,

  daytype

Resident calendar;

Left Join(T1)

Intervalmatch(date)

LOAD start, close resident C1;

Join(T1)

Intervalmatch(date)

LOAD * resident C1;

drop table C1;

You may need some cleanup by dropping cases/calendar source tables or adjust table names to integrate with your existing load script/data model.

6 Replies

Re: transfer SQL query to Qlikview script

You can copy paste same in Qlik. It will work

MVP
MVP

Re: transfer SQL query to Qlikview script

Assuming you have resident tables cases and calendar, the using an interval match:

C1:

NoConcatenate

LOAD id,

  start,

  close

Resident cases

T1:

NoConcatenate

LOAD date,

  daytype

Resident calendar;

Left Join(T1)

Intervalmatch(date)

LOAD start, close resident C1;

Join(T1)

Intervalmatch(date)

LOAD * resident C1;

drop table C1;

You may need some cleanup by dropping cases/calendar source tables or adjust table names to integrate with your existing load script/data model.

vinieme12
Esteemed Contributor II

Re: transfer SQL query to Qlikview script

You can use the same SQL SELECT

FACT:

SQL SELECT id,start,close,count(daytype) as dayscount

FROM [dbo].[cases]

inner join [dbo].calendar on (calendar.Date>cases.Start and calendar.Date<=cases.close)

where daytype='1'

group by  start,close,id,daytype

order by cases.start desc;

parispantelis
New Contributor II

Re: transfer SQL query to Qlikview script

unfortunately it doesn't work...

the sql server does not exist anymore...

parispantelis
New Contributor II

Re: transfer SQL query to Qlikview script

unfortunately it doesn't work...

the sql server does not exist anymore...

parispantelis
New Contributor II

Re: transfer SQL query to Qlikview script

i made a few changes and it's working fine

thanks Jonathan

Community Browser