topic Re: issue with canonical date implementation in App Development

issue with canonical date implementation

ioannaiogr — Thu, 11 May 2023 16:45:59 GMT

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.

Re: issue with canonical date implementation

Kushal_Chawda — Thu, 11 May 2023 20:48:22 GMT

@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;

Re: issue with canonical date implementation

ioannaiogr — Thu, 11 May 2023 20:34:28 GMT

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

Re: issue with canonical date implementation

Kushal_Chawda — Thu, 11 May 2023 20:45:02 GMT

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

Re: issue with canonical date implementation

ioannaiogr — Fri, 12 May 2023 09:45:16 GMT

@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?

Re: issue with canonical date implementation

Kushal_Chawda — Fri, 12 May 2023 10:12:20 GMT

@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.

Re: issue with canonical date implementation

ioannaiogr — Fri, 12 May 2023 10:20:54 GMT

@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!🤔

Re: issue with canonical date implementation

Kushal_Chawda — Fri, 12 May 2023 10:26:44 GMT

@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.

Re: issue with canonical date implementation

ioannaiogr — Fri, 12 May 2023 10:37:07 GMT

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