Load records after a specif date for a specific field

juanpv20 — Fri, 06 Jan 2017 21:46:25 GMT

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

Re: Load records after a specif date for a specific field

Nicole-Smith — Fri, 06 Jan 2017 22:46:19 GMT

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.

topic Load records after a specif date for a specific field in App Development

Load records after a specif date for a specific field

Re: Load records after a specif date for a specific field