Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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;
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.