I am analyzing dummy data about patient occupancy in an institute. A part of the data that I have is:
| PatiëntAchternaam | PatiëntGeboortedatum | PatiëntGeslacht | PatiëntId | PatiëntLand | PatiëntVoornaam | VerblijfStart | VerblijfEind | VerblijfStartNum | VerblijfEindNum |
| Aarle | 18-5-1999 | man | 8 | Nederland | Erwin | 3-6-2015 | 3-8-2015 | 42158 | 42219 |
| Alink | 8-11-1987 | man | 24 | Nederland | Reinier | 29-1-2017 | 4-7-2017 | 42764 | 42920 |
| Aris | 8-9-2000 | man | 30 | Nederland | Meine | 28-2-2017 | 9-9-2017 | 42794 | 42987 |
| Assink | 6-3-1993 | vrouw | 22 | Nederland | Robin | 19-3-2017 | 18-10-2017 | 42813 | 43026 |
| Assink | 4-10-2000 | man | 10 | Nederland | Leonard | 11-11-2016 | 25-11-2016 | 42685 | 42699 |
| Borst | 4-8-2001 | man | 5 | Belgie | Joost | 22-3-2015 | 16-4-2015 | 42085 | 42110 |
| Bouwmeester | 9-9-1977 | man | 1 | Nederland | Koen | 1-1-2015 | 15-2-2015 | 42005 | 42050 |
| Brons | 18-1-1989 | man | 25 | Nederland | Roos | 1-1-2016 | 8-1-2016 | 42370 | 42377 |
| Craig | 1-1-1977 | vrouw | 29 | Australië | Brittany | 7-5-2017 | 6-3-2017 | 42862 | 42800 |
I try to make a new table that contains the complete range of NumDate and for each day the number of patients that are present. Before I could sold with a Python script but now I want to do this in QLikView itself. A part of the data presentation that I want is :
| DatumNum | PatiëntId |
| 42005 | 1 |
| 42006 | 1 |
| 42007 | 1 |
| 42008 | 1 |
| 42009 | 1 |
| 42010 | 1 |
| 42011 | 1 |
| 42012 | 1 |
| 42013 | 1 |
| 42014 | 1 |
| 42015 | 1 |
| 42016 | 1 |
| 42017 | 1 |
| 42018 | 1 |
| 42019 | 1 |
| 42020 | 1 |
| 42021 | 1 |
| 42022 | 1 |
| 42023 | 1 |
| 42024 | 1 |
| 42025 | 1 |
| 42026 | 1 |
| 42027 | 1 |
| 42028 | 1 |
| 42029 | 1 |
| 42030 | 1 |
| 42031 | 1 |
| 42032 | 1 |
| 42033 | 1 |
| 42034 | 1 |
| 42035 | 1 |
| 42036 | 1 |
| 42037 | 1 |
| 42037 | 13 |
| 42037 | 28 |
| 42038 | 1 |
| 42038 | 13 |
| 42038 | 28 |
| 42039 | 1 |
| 42039 | 13 |
| 42039 | 28 |
As shown, there is one patient present from 42005 until 42036 and from 42037 and on there are three patients present, given by the patientId.
So far, my script to do this is :
DatumRange:
LOAD DatumNum
Resident Kalender;
BeginEindVerblijf:
LOAD VerblijfStartNum, VerblijfEindNum, PatiëntId
Resident Patiënt;
Left join(DatumRange)
IntervalMatch(DatumNum, VerblijfStartNum, VerblijfEindNum, PatiëntId)
Load
VerblijfStartNum,
VerblijfEindNum,
PatiëntId
Resident BeginEindVerblijf;
Directory;
Bezetting:
LOAD DatumNum, Date(DatumNum, 'YYYYMMDD') as JaarMaandDag, PatiëntId
Resident DatumRange;
DROP Table DatumRange;
DROP Table BeginEindVerblijf;
But I recieve the error:
Field not found - <PatiëntId>
Bezetting:
LOAD DatumNum, Date(DatumNum, 'YYYYMMDD') as JaarMaandDag, PatiëntId
Resident DatumRange
To my idea, DatumRange is joined together with the table BeginEindVerblijf, so it has to contain the field PatiëntId.
