issue with canonical date implementation

[ioannaiogr]

Hello all. I'm pretty sure I'm implementing my date bridge incorrectly.

I studied again this post by HIC CanonicalDate  .

I have two tables 

Applications_Fact:

Load ApplicationID,

ApplicationIssueDate,

ApplicationSubmittedDate

ApplicantID;

Applicants_Dimension:

ApplicantID,

ApplicantInsertedDate;

One applicantID can have many applicationIDs. I've looked into it and If I understood correctly, my fine enough grain table is my fact table because one application id has one value of each date associated.

Imagine that my data in fact table are like

ApplicantID    ApplicationID  ApplicationIssueDate   ApplicationSubmittedDate   ApplicantDateInserted

1                                  1                        11/5/2023                                11/5/2023                                       10/5/2023

1                                  2                        12/5/2023                                 11/5/2023                                       10/5/2023

4                                  100                       9/5/2023                                 8/5/2023                                       7/5/2023

In between note: The thing is, that there are applicantID from the dimension table that got inserted in the academicYear (which i have in my canonical calendar which i'll show later), so they have an  ApplicantinsertionDate  BUT they don't have an applicationID in the fact table... So these get ignored. I'm missing these completely. Even if I joined, with the date bridge for applicationID they get LOST and ignored.

Anyway, I did a mapping table as suggested for my Dimension table like:

MapTable:

MAPPING LOAD ApplicantID, ApplicantDateInserted resident Applicants_Dimension;

and a date bridge as counseled:

Load ApplicationID,

     ApplyMap('MapTable',ApplicantID,null()) as CanonicalDate,

    'Applicant_inserted' as DateType 

resident Applications_Facts;

Load ApplicationID,

           ApplicationSubmittedDate as CanonicalDate,

            'Submitted' as DateType

resident  Applications_Facts;

Load ApplicationID,

           ApplicationIssueDate as CanonicalDate,

            'Issued' as DateType

resident  Applications_Facts;

And then linked the CanonicalDate to the CanonicalCalendar. The measures work perfectly for Applications cause I've cross-checked though SQL queries. My issue is the applicants with no applications that go missing... I'm sure it's the date bridge. I pick AcademicYear in CanonicalCalendar and where there is no application in this year, the applicant- POOF- disappears. But If I just pick date range in my ApplicantDateInserted field, the count is as it should be... But i want it to work with DateType='ApplicantDateInserted'.

I hope i'm getting some feedback... I want to know what is my issue.

Sorry for the gigantic post. I was trying to be explaining. Also ,sorry, as this is my 3rd post for this canonical date, but now I'm sure this is the problem.

Thank you in advance.

[Kushal_Chawda · Accepted solution]

@ioannaiogr  I think in your previous post also I had mentioned the approach but not sure did you try it but again I am posting this

Applications_Fact1:
Load ApplicationID,
ApplicationIssueDate,
ApplicationSubmittedDate,
ApplicantID
FROM Source;

Left Join(Applications_Fact1)
LOAD ApplicantID,
     ApplicantInsertedDate
FROM Source;

Applications_Fact:
load *,
     Floor(ApplicationIssueDate)&'|'&Floor(ApplicationSubmittedDate)&'|'&Floor(ApplicantInsertedDate) as Key
Resident Applications_Fact1;

Drop Table Applications_Fact1;

date_bridge:
Load Key,
     date(SubField(Key,'|',1)) as CanonicalDate,
     'IssueDate' as DateType;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

Concatenate(date_bridge)
Load Key,
     date(SubField(Key,'|',2)) as CanonicalDate,
     'SubmittedDate' as DateType;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

Concatenate(date_bridge)
Load Key,
     date(SubField(Key,'|',3)) as CanonicalDate,
     'InsertedDate' as DateType;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

autonumber Key;

[ioannaiogr]

Hello @Kushal_Chawda , with left join, aren't I missing those applicants without applications? 

[Kushal_Chawda]

@ioannaiogr  you can change the join type depending on your requirement. So instead you can just use "Join"

[ioannaiogr]

@Kushal_Chawda wait, what?! What just happened!? My problem is solved thanks to you 😁

I only have one question for this script. Why doesn't it work if in the initial Applications_Facts1 I also load the Key field and it needs a resident load and a drop later? What does that help? 

[Kushal_Chawda]

@ioannaiogr  I thought you have two different table that's why I used join to combine all dates into single table and on resident load created Key using those dates. But, if you already have all dates in one table no need to do the join and resident load.

[ioannaiogr]

@Kushal_Chawda 

No, i have them in 2 tables as you said. With join I have both in a table, but why did you have to resident load the firts then drop it?

Couldn't I do like: 

Applications_Fact1:
Load ApplicationID,
ApplicationIssueDate,
ApplicationSubmittedDate,
ApplicantID
Floor(ApplicationIssueDate)&'|'&Floor(ApplicationSubmittedDate)&'|'&Floor(ApplicantInsertedDate) as Key
FROM Source;
Left Join(Applications_Fact1)
LOAD ApplicantID,
     ApplicantInsertedDate
FROM Source;

 and then connect by Key the CanonicalCalendar and the DateBridge  as you suggested?

I know it doesn't work like that because I tried it before, but my question is why!🤔

[Kushal_Chawda]

@ioannaiogr  because after joining both the tables, resultant table contains all the required fields from both tables combining it to single table, so during joining process we can't refer to the columns which is getting joined in the same load. Hence, we need to take resident load to create a key using 3 date fields referring to previously joined table.

[ioannaiogr]

@Kushal_Chawda I see. I'll process it and If I have more questions I'll get back to you. Thank you so much!