Looking up Name of a user based on a date in another column

[aczeitner]

Hey Specs !

I want to use text associations that is time dependant on a date in the fact table

So for example let just Say I have a report where my yearly salary can be found from 2020 to 2024.

Let's say I 've changed my name from Dana to Lara on the first of 2023 so in the report I want to see something like this.

Year ---- Name --- Yearly Salary

2020---- Dana ---- 2000 USD

2021---- Dana ---- 2200 USD

2022---- Dana ---- 2700 USD

2023---- Lara ---- 3500 USD

2024---- Lara ---- 4000 USD


I have the text table that looks like this

ID --- ValidFrom ---- -Name

1 ---- 10000101 ----- Dana

1 ----- 20230101 ----- Lara


I could use simple join to write the name in a column in every line but I was planning to use time dependant Text associations.

However I failed to use this as far as I noticed it's automatily use the last name that I have in the text table eventhough I set the time dependancy.

Can I achieve the behaviour that i was showing up with Text associations (or dimension associations) ?
And if yes How ? the only blog post I've found made the text associations refer on a specific date.
But that would make all my Row contain the name Lara even in 2020 when my name was Dana and that would be incorrect result.

Any help much appriciated
Thanks Andris

[Abhishek_Hazra]

Hi aczeitner,

You have different granularity of time data eg. year & date range. This is not possible with association in a graphical approach. Are you using SQL view? If yes then you can use temporal join operation for that.

Please refer to scenario #3 in following blog post : https://blogs.sap.com/2022/11/10/build-historization-and-track-data-changes-with-sap-data-warehouse-...

Hope this helps 🙂

Best Regards,

Abhi

[aczeitner]

Sorry I saw you response but I was not able to login into sap blog for some reason for several days.

What you linked was very helpful and thank you for that but let's keep to the original subject what if the granuality same then both uses Calday