topic Re: Two calendars in QlikView

Two calendars

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Two calendars

Miguel_Angel_Baeyens — Wed, 19 Oct 2016 10:24:48 GMT

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

Re: Two calendars

Anonymous — Wed, 19 Oct 2016 11:41:19 GMT

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

Re: Two calendars

Anonymous — Wed, 19 Oct 2016 11:51:58 GMT

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 !

Re: Two calendars

Anonymous — Wed, 19 Oct 2016 11:55:39 GMT

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.

Re: Two calendars

Anonymous — Wed, 19 Oct 2016 12:10:25 GMT

Hello Marcio,

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

But thank you!

Re: Two calendars

Miguel_Angel_Baeyens — Wed, 19 Oct 2016 21:00:57 GMT

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

Re: Two calendars

Anonymous — Thu, 20 Oct 2016 05:39:56 GMT

It worked!

Very good explain.

Thank you very much Miguel.

Re: Two calendars

Anonymous — Thu, 20 Oct 2016 10:48:24 GMT

Your wellcome.