3 Replies Latest reply: Nov 30, 2017 2:50 PM by omar bensalem RSS

    how to link a master calender?

    Bernhard Krippl

      Hello Community,

       

      I am still very new and inexperienced in dealing with Qlik. But now I've been working a bit, but would now end up with my still modest Latin.

       

      The following: I have two SQL queries from a system. One about the entrances to the company and in a second query the withdrawals from the company.

       

      Here are my queries:

      1) Entries: Select cost Center, cost Center Name, Company Name, entry year, entry month, "total entries per month", month Name

       

      2) Withdrawal: Select cost Center, cost Center Name, Company Name, Exit year, exit month, "total Exit per month", month Name

       

       

       

      This is my current calender i tried:

      Load

      floor(num(??) as %calender_year_employee

      *

      from ..\..\..\Tools\1QVDPicture\Calender.qvd

       

      At the moment I have created one list for entries and one for exits. Nevertheless, I need one calender for each report (one calender which is linked to the entry year, entry month and the other ones which is linked to the exit year, exit month)

       

       

      Honestly I would prefer that the people have to select out of one calender and both report-lists will change appropriate.

      However, now i dont know what to put in the brackets above (calender) and how to link with my entry year, entry month and exit year, exit month.

       

      Thanks for your help.

       

        • Re: how to link a master calender?
          youssef belloum

          Hi,

           

          There is a lot a options here on how to link your calendar table to your fact tables.

           

          for example here, you have two fact tables (entrances ansd withdrawals),

           

          you can for example:

           

          • create a date field from the month and the year on each table  EXAMPLE ==>(Date(MakeDate(Year, Month, 1), 'MMM-YYYY')
          • concatenate these two tables (in one fact table)
          • create a calendar table with autogenerating all the dates from the first date to lastest date present on the fac table

           

          like this you will have on date linked to one entrance date and one withdrawal data simultaneously, and so compare your entrances with your withdrawals with a time axis.

           

          ALL THIS IS ONE APPROACH !

          • Re: how to link a master calender?
            omar bensalem

            It would be sthing like this:

             

            here's a script you can use to build a test application and understand how to handle different dates:

            SET DateFormat='YYYY-M-D';

             

             

            Table1:

            LOAD * INLINE [

            ID, Seq, ActivityDate, Value1

            1, 1, 2011-5-10,10

            1, 2, 2011-5-12,2

            2, 1, 2011-5-12,20

            3, 1, 2011-6-15,40

            ];

             

             

            Table2:

            LOAD recno() as ID, * INLINE [

            DepartureDate, ArrivalDate, Value2

            2011-6-15,2011-6-16,5

            2011-6-15,2011-6-17,10

            2011-6-16,2011-6-16,20

            ];

             

             

            OtherTable:

            LOAD recno() as OtherID, * INLINE [

            OtherDate, OtherValue

            2011-5-12,2

            2011-6-15,3

            2011-6-17,5

            ];

             

             

             

             

            Link:

            LOAD

            ID

            ,Seq

            ,ActivityDate as Date

            ,'Activity' as DateType

            RESIDENT Table1

            ;

            CONCATENATE (Link)

            LOAD

            ID

            ,DepartureDate as Date

            ,'Departure' as DateType

            RESIDENT Table2

            ;

            CONCATENATE (Link)

            LOAD

            ID

            ,ArrivalDate as Date

            ,'Arrival' as DateType

            RESIDENT Table2

            ;

            CONCATENATE (Link)

            LOAD

            OtherID

            ,OtherDate as Date

            ,'Other' as DateType

            RESIDENT OtherTable

            ;

             

             

            Calendar:

            LOAD *

            ,date(monthstart(Date),'MMM YYYY') as Month

            ;

            LOAD date(makedate(2011,5,1)+recno()-1) as Date

            AUTOGENERATE 61

            ;

             

             

             

            Capture.PNG

            Capture.PNG