topic Re: Compare 2 dates in the same column based on dimensions in App Development

Compare 2 dates in the same column based on dimensions

RogerG — Tue, 26 Apr 2022 22:13:12 GMT

Hello all,

I have a dataset like this

ID	Project	Timepoint	Date
1	P1	T1	1/1/2022
1	P1	T2	2/1/2022
2	P2	T1	5/1/2023
1	p3	T1	1/1/2022
1	p3	T2	2/15/2022
1	p3	T3	3/1/2022

What I want to do is create an output table that would show

(I don't like tables anymore 🙂

ID Project Timepoint Match

Only thing that would show under this is the mismatches

ID Project T1 T2 T3 Aligned

This would show blank for matches and the difference in duration for mismatches, per day, not minutes. if all were aligned, it would show a True or False

Credit for the date matches

Extra credit for the table. I am not sure if it would be better to do the cross table in the load script or work with it in a pivot table

Thoughts?

Re: Compare 2 dates in the same column based on dimensions

hic — Wed, 27 Apr 2022 07:51:42 GMT

It is unclear what you mean with "aligned", but the following structure could probably help you. Note that the dime difference is calculated in the last step, and this you can use for your definition of "aligned".

SourceData:
Load
ID,
Project,
Timepoint,
Date#(Date,'M/D/YYYY') as Date
Inline
[ID,Project,Timepoint,Date
1,P1,T1,1/1/2022
1,P1,T2,2/1/2022
2,P2,T1,5/1/2023
1,p3,T1,1/1/2022
1,p3,T2,2/15/2022
1,p3,T3,3/1/2022];

FlattenedTable1:
Load distinct ID, Project Resident SourceData;
Left Join (FlattenedTable1)
Load ID, Project, Date as T1 Resident SourceData Where Timepoint='T1';
Left Join (FlattenedTable1)
Load ID, Project, Date as T2 Resident SourceData Where Timepoint='T2';
Left Join (FlattenedTable1)
Load ID, Project, Date as T3 Resident SourceData Where Timepoint='T3';

Drop Table SourceData;

FlattenedTable2:
Load
T2-T1 as Diff_T1_T2,
T3-T1 as Diff_T1_T3,
*
Resident FlattenedTable1;

Drop Table FlattenedTable1;

Re: Compare 2 dates in the same column based on dimensions

RogerG — Wed, 27 Apr 2022 13:55:35 GMT

Sorry, I see the confusion here. What we have is 2 project, on created from the other (ID) I want to see it T1 date in one project matches the T1 date in the second project for the same ID (aligned)