I am trying to load data into a QVW using 2 select statements, the second select statement will limit what it loads based on the data in the first.
So basically i want code like this:
(imagine ColA, Col1 and Col2 are dates, as dates are what i want in this example)
SQL SELECT
ColA
ColB
ColC
FROM TableA
SQL SELECT
Col1
Col2
Col3
FROM Table1
WHERE ColA BETWEEN Col1 AND Col2
Now, i didnt try the code above, but in my head that code wont work, as i cant reference the table that isn't created yet (if that makes sense) so, i thought of a resident load.
So, in my head i would use this code:
TblA:
SQL SELECT
ColA
ColB
ColC
FROM TableA
Tbl1:
SQL SELECT
Col1
Col2
Col3
FROM Table1
Tble1:
LOAD * RESIDENT Tbl1
WHERE WHERE ColA BETWEEN Col1 AND Col2
But, when i try this, the code is underlined with errors, and now im a little stuck as to whether or not WHERE clauses can be used this way in resident loads. If they are, how would i go about using it? If not What is the best way to achieve my goal.
Your logic doesn't really work. You want to filter TableA load on ColA where its value is between Col1 and Col2 in another table, however there will be multiple rows (I assume) for ColA to match against so is there some other link between the tables? Perhaps an example might help.
Thank for your replies, i realise that it wouldnt work like that, hense why i asked. I was hoping that may at least describe what i was trying to achieve.
In the examples given ColA will be the date a test was done. Col1 is Admission Date and Col2 is Discharge Date.
The problem i have is that the 2 systems i am using do not have a common link between them. All i can do is link on patient and ward. however as a patient can be on a ward more than once, i was trying to only load data where there was a test between admission and discharge.
however, the more i think on this, the the more it seems liek this wont work, and as said above, i will probably have to write a SQL statement with the joins in, rather than allowing Qlikview to join itself.
Sorry for the time wasting, but that will be the way i progress, it hurts my head less.
I think Qlikview might be easier in this scenario, you can add the PatientID key into the intervalmatch, then join the ward id back in based on patientID, WardStart and End dates ...
