Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
You can copy paste same in Qlik. It will work
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.
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;
unfortunately it doesn't work...
the sql server does not exist anymore...
unfortunately it doesn't work...
the sql server does not exist anymore...
i made a few changes and it's working fine
thanks Jonathan