Get active person by selecting a date
Good afternoon,
probably someone has a good instruction guide or example on how to create cubes to resolve this:
I have a mssql table "person" and "person-activity". In the table "person" I have the name, code of the person
and other anagraphic data of the person. In the table "person-activity" I have the code of the person and the date of
the beginning of the activity and the end date of the activity.
In Board I would like to select a day and see all the active person in a dataview.
Example 1:
a) I select the 20.01.2018
b) table person-activity:
person a - date of entry = 10.01.2018, end-date = 21.01.2018
person b - date of entry = 01.05.2018, end-date = 20.05.2018
c) the result = person a
Example 2:
a) I select the 22.01.2018
b) table person-activity:
person a - date of entry = 10.01.2018, end-date = 21.01.2018
person b - date of entry = 01.05.2018, end-date = 20.05.2018
c) the result = any person
Is this possible or do I have to prepare the data before the import? For example an import day by day with all the active person?
Thanks for your help,
Egon.
Answers
-
Hi Egon Santoni,
a solution can be a simple sql datareader linked to a cube structured by day-person.
When you read the data, go to manual mode and try this query (in [...] please insert the fields of your table):
select dateadd(day, [starting_date], number), [person_id], '1'
from master..spt_values n with (nolock), person-activity
where type='p' and and number between 0 and datediff([starting_date], [ending_date]
With this solution I think that you will write into your cube a 1 per each day of "presence" of the single person.
Feel free to comment with your solution when it run.
Regards,
3 -
Hi Egon Santoni, one option to perform the logic inside BOARD is to load both tables as they are and perform the processing via dataflow action of a procedure.
For this you need 4 cubes:
A Begin Date (Date type cube dim by Person) - populate from SQL
B End Date (Date type cube dim by Person) - populate from SQL
C Day (Date type cube dim by day only) - populate via dataflow using Day = day() function to hold date values for every day
D Active Flag (Integer cube by Day by Person)
To populate D, you need IF statement =if(and(c=>a,c=<b),1,0)
You can then multiply any anagraphic data by this ACTIVE flag mapping to have only values shown in days the Person was active, or even use the Active Flag as a filter in the layout depending on what you are trying to do.
I hope it helps!
Thanks,
Audrey
3
