Reproducing SQL-type statement in Script Load

[#1]

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

[flipside]

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.

[#3]

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!