Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone
This is a long shot, but I can't work out how to replicate the following SQL in QlikView. For the purposes of this explaination, I've got two tables: S and R. Currently, I'm loading them in as:
NoConcatenate
NewTable:
Load
Sum(EQ) as SUMEQ,
Sum(EUQ) AS SUMEUQ,
DateField,
Lookup('GroupName','PersonID',FK_Person_ID,'S') as [Group]
Resident R
group by Lookup('GroupName','PersonID',FK_Person_ID,'S'),DateField;
However, I need to introduce a where clause that ensures that the DateField from table R is between two dates from table S. In SQL, I'd do it like the below:
SELECT Sum(r.EQ),Sum(r.EUQ), r.DateField, s.Group
From r
join s on s.personId = r.FK_Person_ID and r.DateField between s.LowerDate and s.UpperDate
group by s.Group, r.DateField
I can't work out how to do such a join in QlikView. Any help is much appreciated; I'm losing sleep over this one!
Cheers
You can't do SQL commands on resident tables if that's what you mean, but you could Store the tables to a text file then work on that. I can't comment on the performance of this type of connection though as have only played with it. I only use it for quick checking SQL code as I don't have ready access to a SQL or MS Access database.
AFAIK, there are some limitations - limited sub-joins may be one of them, and NULL handling is different.
Ah it was worth a try! The sheer volume of data we've got (hundreds of thousands of rows) would probably rule out storing the data as a text file. I think I've got the correct data now, I'm just trying to remove some records using ANOTHER attempt at something I can do in SQL but not QV!