Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RogerG
Creator
Creator

Compare 2 dates in the same column based on dimensions

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

or 

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?

 

Labels (1)
2 Replies
Henric_Cronström

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;

RogerG
Creator
Creator
Author

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)