Summing up entries of linked tables

[ynottableau]

Hi All,

I have 2 tables. Table #1 contains the primary form. The table #2 tracks the status of those forms as they are routed and the number of days they sat at each stage. I want to construct a third table linking the previous two, table #3 should show total number of days from all stages.

Name	ID
RefA	aa1
RefB	aa2
RefC	aa3

ID	Status	Date	ElapsedDays
aa1	Completed	5/12/2023	3
aa1	Pending	5/9/2023	8
aa1	Started	5/1/2023	1
aa2	Pending	5/6/2023	1
aa2	Started	5/5/2023	5
aa3	Removed	5/10/2023	9
aa3	Started	5/1/2023	1

Results:

Name	Total ElapsedDays
RefA	12
RefB	6
RefC	10

[BrunPierre]

A scripting approach is as follows.

T1:
Mapping LOAD ID, 
        Name
FROM Table1;

T2:
LOAD ID, 
     Status, 
     Date, 
     ElapsedDays,
     ApplyMap('T1',ID,Null()) as Name
FROM Table2;

NoConcatenate
T3:
LOAD Name,
Sum(ElapsedDays) as "Total ElapsedDays"

Resident T2
Group By Name;

DROP Table T2;

[NiTo · Accepted solution]

hi,

Simple approach is to first load both the tables and linked them using ID. Then open up a sheet, select table and select Name as dimension and in measure type the followling:

aggr(sum(ElapsedDays),Name)

It works for me, hope it will work for you too.