Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
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
(
Group By VisitID;
table1: //FirstVisitedDates
LOAD VisitID,
FirstVisitDate,
Applymap('table2',VisitID) - FirstVisitDate as DateDiff
FROM
(
Great help - thanks everybody - i will try this out!