Two calendars

[Anonymous]

Hello!

It´s a simple question.

I have two calendars realted in a table

In_calendar                  Out_calendar                         Workers

Date_in ,                     Date_out,                              Date_in

In_year,                        Out_year                               Date_out

In_Month,                     Out_month                            ID

I would like to have in one calendar the two calendars, I would select for exemple January 2016 and see all the dates for Date_in and all the dates for Date_out for the select month and year.

Thank you!

[Miguel_Angel_Baeyens]

Guillermo,

Although you could create indeed the two calendars, I think that the solution you are looking for is closer to this one:

help using a 'BETWEEN' join on tables from different datasources

Miguel

[Anonymous]

Hello Miguel, thanks for your answer.

If I´ve understood your solution, I should do an Interval match between my start date and end date with wich data?

I mean, I your exemple, in the Fact table you have a Date, but in my case, my only dates are IN and OUT. There isn´t any "central" data in my fact table.

If I´m wrong please let me know

[Anonymous]

You can concatenate the tables something like:

In_calendar:
Load 
Date_in ,   
In_year as Year,    
In_Month as Month
from yourtable...;   

// Out_calendar
concatenate(In_calendar)
Load
   Date_out,
    Out_year as Year,
  Out_month  as Month
from YourOtherTable...; 

If you select the Year and Month you should get In and Out Dates !

[Anonymous]

Take a look at the example I am sending.

I have several dates but I can control them by a generic.

I hope it helps.

[Anonymous]

Hello Marcio,

I can´t open local qvw documents, I don´t have the licence.

But thank you!

[Miguel_Angel_Baeyens · Accepted solution]

Yes, you do have a "central" date in the fact table. Those are Date_in and Date_out, since you want to click on any given month i.e.: "January" and see all Date_in and Date_out which match with that range (a month is "just" a starting and an end date).

But since you also have both dates in the Workers table, you can just add the Month(), Year(), etc. functions in the fact table, along with the dates. However, when you click on "January", you will see either dates in or dates out, not both, that's why you need that IntervalMatch() using both in and out dates.

However, I don't know which charts you will use or further, which type of analysis you want to perform, it could be done too if you have your Workers table split into two and then concatenated to have one single date per row in the fact table, and you can easily add a "Type" field with values "In" and "Out", and then apply the Month(), Year() and other date functions on that single date field.

Schematically, it would read in the script like:

WorkersTemp:
LOAD *,
Date_in AS Date,
'In' AS Type
FROM Workers.qvd (qvd)
WHERE Len(Trim(Date_in)) > 0;
CONCATENATE (WorkersTemp) LOAD *,
Date_out AS Date,
'Out' AS Type
FROM Workers.qvd (qvd)
WHERE Len(Trim(Date_out)) > 0;

DROP FIELDS Date_in, Date_out;

STORE WorkersTemp INTO Workers_Single_Date.qvd (qvd);

DROP TABLE WorkersTemp;

Workers:
LOAD *,
Month(Date) AS Month,
Year(Date) AS Date
FROM Workers_Single_Date.qvd (qvd);

Miguel

[Anonymous]

It worked!

Very good explain.

Thank you very much Miguel.

[Anonymous]

Your wellcome.