How to substract Dates of different tables

[#1]

Hello,

i have 2 tables.

Table 1 has an attribute: First Visitdate

There is a 1:n connection from table 1 to table 2.

Table 2 has: Visitdate (not the first visitdate)

Now i would like to create a table which shows the time (in days) between the first visit of a person and all following visits.

So basically.

If the first visit date is 20.10.2016

And table 2 includes:

21.10.2016

22.10.2016

24.10.2016

Then the new table should look like:

Days:

1

2

4

Not sure yet how to do it and how to connect the tables properly / substract the dates.

[Gysbert_Wassenaar · Accepted solution]

Join the two tables so all the fields necessary for the calculation exist in one table. Then you can subtract the values.

T1:

LOAD VisitID, FirstVisitDate FROM table1;

JOIN (T1)

LOAD VisitID, VisitDate FROM table2;

T2:

LOAD *, Interval(VisitDate - FirstVisitDate, 'd') as Days RESIDENT T1;

DROP Table T1;

[avinashelite]

If you have a primary key common in both the tables then you could try with the mapping load and map the same to first table and load them ..

Table2:

Mapping load

Key,Date2

..

Table1:

LOAD

Key,

Date1-(applymap('Table2',Key)) as Difference,

Date1

[vinieme12]

If your tables are associated, you could do this directly in the chart

OR

In Script

table2:  ///MultipleDates
Mapping LOAD VisitID,
DATE(FirstSortedValue(VisitDateS,-VisitDateS),'MM/DD/YYYY') as maxdate
FROM

(ooxml, embedded labels, table is Sheet3)
Group By VisitID;



table1:  //FirstVisitedDates
LOAD VisitID,
FirstVisitDate,
Applymap('table2',VisitID) -  FirstVisitDate as DateDiff
FROM

(ooxml, embedded labels, table is Sheet2);

[#5]

Great help - thanks everybody - i will try this out!