topic transfer SQL query to Qlikview script in QlikView

transfer SQL query to Qlikview script

parispantelis — Wed, 13 Dec 2017 10:30:23 GMT

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.

Re: transfer SQL query to Qlikview script

Anil_Babu_Samineni — Wed, 13 Dec 2017 11:36:54 GMT

You can copy paste same in Qlik. It will work

Re: transfer SQL query to Qlikview script

jonathandienst — Wed, 13 Dec 2017 11:50:15 GMT

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.

Re: transfer SQL query to Qlikview script

vinieme12 — Wed, 13 Dec 2017 12:05:24 GMT

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;

Re: transfer SQL query to Qlikview script

parispantelis — Sat, 16 Dec 2017 20:21:41 GMT

unfortunately it doesn't work...

the sql server does not exist anymore...

Re: transfer SQL query to Qlikview script

parispantelis — Sat, 16 Dec 2017 20:21:57 GMT

unfortunately it doesn't work...

the sql server does not exist anymore...

Re: transfer SQL query to Qlikview script

parispantelis — Sun, 17 Dec 2017 09:37:14 GMT

i made a few changes and it's working fine

thanks Jonathan