Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to load the data for each device only if the events were created after the process date
For example
This is the inputs data tables:
Table with the processdate
device | processdate |
d1 | 01-01-17 |
d2 | 01-02-17 |
d3 | 01-03-17 |
d4 | 01-04-17 |
d5 | 01-05-17 |
Table with the events
device | event | dateevent |
d1 | e1 | 01-01-17 |
d1 | e2 | 01-01-17 |
d1 | e3 | 01-03-17 |
d1 | e3 | 01-05-17 |
d2 | e2 | 01-01-17 |
d2 | e4 | 01-05-17 |
d3 | e2 | 01-01-17 |
d3 | e1 | 01-03-17 |
d4 | e2 | 01-01-17 |
d4 | e3 | 01-05-17 |
d4 | e4 | 01-03-17 |
d5 | e5 | 01-01-17 |
d5 | e6 | 01-03-17 |
d5 | e2 | 01-05-17 |
Then I want to load all the events only if the dateevent is after the processdate. Then the data I should have in my app will be only
device | event | dateevent |
d1 | e1 | 01-01-17 |
d1 | e2 | 01-01-17 |
d1 | e3 | 01-03-17 |
d1 | e3 | 01-05-17 |
d2 | e4 | 01-05-17 |
d3 | e1 | 01-03-17 |
d4 | e3 | 01-05-17 |
d5 | e2 | 01-05-17 |
That will prevent my app to work with data before the process event.
I will be something like:
PROCESSADOS:
LOAD
device,
processdate
FROM table with the process date
(qvd);
DATA:
if dateevent > process date
LOAD
device,
event,
dateevent
FROM table with the events
(qvd);
This should do the trick:
Device:
LOAD * INLINE [
device, processdate
d1, 01-01-17
d2, 01-02-17
d3, 01-03-17
d4, 01-04-17
d5, 01-05-17
];
Event:
LOAD * INLINE [
device, event, dateevent
d1, e1, 01-01-17
d1, e2, 01-01-17
d1, e3, 01-03-17
d1, e3, 01-05-17
d2, e2, 01-01-17
d2, e4, 01-05-17
d3, e2, 01-01-17
d3, e1, 01-03-17
d4, e2, 01-01-17
d4, e3, 01-05-17
d4, e4, 01-03-17
d5, e5, 01-01-17
d5, e6, 01-03-17
d5, e2, 01-05-17
];
LEFT JOIN (Event)
LOAD device, processdate
RESIDENT Device;
Event_Final:
NOCONCATENATE LOAD device, event, dateevent
RESIDENT Event
WHERE dateevent >= processdate;
DROP TABLE Event;
device | event | dateevent |
---|---|---|
d1 | e1 | 01-01-17 |
d1 | e2 | 01-01-17 |
d1 | e3 | 01-03-17 |
d1 | e3 | 01-05-17 |
d2 | e4 | 01-05-17 |
d3 | e1 | 01-03-17 |
d4 | e3 | 01-05-17 |
d5 | e2 | 01-05-17 |
I've also attached an example file.