Load records after a specif date for a specific field

[juanpv20]

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);

[Nicole-Smith]

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.