topic Re: How to substract Dates of different tables in QlikView

How to substract Dates of different tables

Wed, 25 Nov 2020 16:16:04 GMT

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:

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

Re: How to substract Dates of different tables

Gysbert_Wassenaar — Tue, 25 Oct 2016 08:55:14 GMT

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;

Re: How to substract Dates of different tables

avinashelite — Tue, 25 Oct 2016 09:02:46 GMT

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

Re: How to substract Dates of different tables

vinieme12 — Tue, 25 Oct 2016 09:42:42 GMT

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

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

Re: How to substract Dates of different tables

Tue, 25 Oct 2016 10:59:02 GMT

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