7 Replies Latest reply: Oct 19, 2017 10:42 AM by Justin Dallas RSS

    Create Start date / end date from same date field

    Ashish Palkar

      Hi There

       

      I have requirement to create Start Data and End date from same date field from database.

       

      Upon selecting date range in app , data should be display based on selected date range.

       

      Can you please suggest how to do it in Qlik Sense. 

       

      Thanks in advance, 

        • Re: Create Start date / end date from same date field
          Justin Dallas

          You would probably need to have a MasterCalendar with a Join table between the Calendar and your Fact table.  This would probably also require that your join table be a RolePlaying table too.  Here is an example script.

           

           

           

          WidgetProductionFact:
          Load * Inline
          [
          'WidgetId', 'Widget Name', 'Widget Class', 'Start Date', 'End Date',
              1, 'Dovakhlin Ice Widget', 'Ice', '08/21/2017','09/20/2017',
              2, 'Call of WidgiDuty', 'Video Game', '08/03/2017','12/20/2017',
              3, 'Widgimon', 'Video Game', '08/03/2017','12/20/2017' 
          ]
          ;
          
          WidgetProductionDateRPTable:
          LOAD WidgetId, [Start Date] AS %widgetDate, 'Start Date' AS RoleType Resident WidgetProductionFact
          ;
          LOAD WidgetId, [End Date] AS %widgetDate, 'End Date' AS RoleType Resident WidgetProductionFact
          ;
          
          DROP FIELDS [Start Date], [End Date] FROM WidgetProductionFact
          ;
          
          MasterCalendar: 
          Load 
           TempDate AS '%widgetDate', 
           DayStart(TempDate) as CalDate,
           Week(TempDate) As Week, 
           Year(TempDate) As Year, 
           Month(TempDate) As Month, 
           Day(TempDate) As Day, 
           'Q' & ceil(month(TempDate) / 3) AS Quarter, 
           Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
           WeekDay(TempDate) as WeekDay 
          ;
          
          //=== Generate a temp table of dates === 
          LOAD 
           date(mindate + IterNo()) AS TempDate
           ,maxdate // Used in InYearToDate() above, but not kept 
          WHILE mindate + IterNo() <= maxdate;
          
          //=== Get min/max dates from Field ===/
          LOAD
           min(FieldValue('%widgetDate', recno()))-1 as mindate,
           max(FieldValue('%widgetDate', recno())) as maxdate
          AUTOGENERATE FieldValueCount('%widgetDate');
          
          EXIT Script
          ;
          

           

          You would then provide the user with the ability to select on the properties of the Calendar table.

          • Re: Create Start date / end date from same date field
            Anil Babu Samineni

            How you are going to work? Let's take condition like if we have one date field called "Date" and then how you need to use from this Date field to Start and End dates. Can you confirm once your intention is here.

             

            What you shown in image those are simple objects, But qliksense doesn't have this future we need to set up Extension for that

            • Re: Create Start date / end date from same date field
              Justin Dallas

              Whoops, I totally misread your question.  Now that I've read some of your comments, I'm not sure how to make a field searchable range-wise i.e 'Show me all baseball games that have FirstPitchDate between a StartDate and EndDate".