Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
how your master calendar is linked to your fact table ? with which field ?
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
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
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?
Yes please, attach an example it will be easier to understand
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
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 ?
can you show it on base of example?
I'll show you how to do it on the script