Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
parispantelis
Contributor II
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
jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
Anil_Babu_Samineni

You can copy paste same in Qlik. It will work

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vinieme12
Champion III
Champion III

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
parispantelis
Contributor II
Contributor II
Author

unfortunately it doesn't work...

the sql server does not exist anymore...

parispantelis
Contributor II
Contributor II
Author

unfortunately it doesn't work...

the sql server does not exist anymore...

parispantelis
Contributor II
Contributor II
Author

i made a few changes and it's working fine

thanks Jonathan