Concatenation of field with mastercalendar

[beck_bakytbek]

Hi Folks, i have a question, my business situation does look like:

I have a Mastercalender with following Fields and Values:

Year = 2018

Year Month = 2018 02,

And i have a separate table with fields: Date_of_Status, this Field consist of information when my project expires : for instance

Project, Date_of_Status:

Project A, 05.06.2017

Project B, 06.05.2018

Project A, 12.12.2017

My Issue is: how can i concatenate this Field with my mastercalender, i am pursuing a goal where if i select my mastercalender and if my Value from mastercalender is large then the value from Field: Date_of_Status then should be created a additional field: for instance: Active and Not active:

Calculation and logic does look like: if i select my calendar

Mastercalender     Value from Field: Date_of_Status        Status            Explanation

2018 02                            06.05.2018                          activ              (here is my project active, because: 06.05.2018 > 2018 02)

2018 02                            05.06.2017                          not active       (here is my project non active, because: 05.06.2017 < 2018 02)

if this logic is done, i want to create the filter: Status : active and non active, So i can select all active and non active project in depende of my master calendar.

Does anybody have any idea? if my explanation not clear enough, please let me know

Thanks a lot

Beck

[YoussefBelloum]

Hi,

how your master calendar is linked to your fact table ? with which field ?

[beck_bakytbek]

Hi youssef, thanks a lot for your responce, i concatenated my facttable with my calendar on the base of datefield. if my explanation is not clear enough, please let me know

[YoussefBelloum]

you mean you LINKED your fact table with your calendar using the datefield ?

because as described above, you didn't mentioned the Date field on your master calendar, you just mentioned the YearMonth field..

did you try to do this on the script ?

If( date_field < date_of_status, 'Active', 'non active') as Status

Ps: be sure that date_field and date_of_status are correctly interpreted as dates and formatted with the right format

[beck_bakytbek]

Hi Youssef,

i can use this expression if i concatenate my field with calender, but i have a question: how can i concatenate the table with calender to make the calculation dynamic or should i create an example for more clarity?

[YoussefBelloum]

Yes please, attach an example it will be easier to understand

[beck_bakytbek]

Hi youssef,

this is my testexample, if it is not enough clear, please let me know, the explanation of logic you can find above

thanks a lot

Beck

[YoussefBelloum]

Ok, from what I can see on your tables structures, you can do this:

on the table "project", if you have all the lines (all the project), you can do a LEFT JOIN on this table from the table "State_of_project", if you have all the line on the "State_of_project" table, you can do a right join etc...Otherwise for getting all the lines from both table, use OUTER JOIN,

all this to get a table with this structure:

Project, Budget, Project_date, Expire_date  ==> to have all the infos on the same line, every row

is it clear for you ?

[beck_bakytbek]

can you show it on base of example?

[YoussefBelloum]

I'll show you how to do it on the script