Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Many Start-End Times columns in a Table

Hello All,

In our project, one of the tables has several "Start Time and End Time" columns.  Like:

         ID,

         A_Start_Time,

         A_End_Time,

         B_Start_Time,

         B_End_Time,

         C_Start_Time,

         C_End_Time, 

          DimensionA,

          etc..

         ...

And the user wanted to be able to select a date range (say "From-To") using the start-end time pair.  I know if there is only one start-end time pair, we can make a link table with the calendar table.  My question is what to do with the above columns?  Which set-analysis fits to this case?

thank you in advance and more power!

Best regards,

Jason

9 Replies
Highlighted
Specialist
Specialist

Just an idea, you can have 3 master calendars for three pairs of start and end dates.

Highlighted
Creator
Creator

Hello Abhijit,

i checked the table again and there are like, so many Start-End Time pairs, so do i have to create a lot of master calendars too?

Thanks

Highlighted
Not applicable

Hi ,

If possible merge all Start_Date & another End_Date in new field respectively to form Single cal along with respective ID Field...

Else

For set-Analysis purpose create Data-Island approach..

Highlighted

Can you reorganise your data model so your Project table has fields for ProjectID and Stage (A,B,C...) and a key that combines these values, ProjectStageKey. (ProjectID & '|' & Stage)

Then create a separate table for the Stages that has the ProjectStageKey plus the Start and End dates.

This way you have single fields for the Start and End date pairs, but can link the start and end of each stage to the relevant project.

Highlighted
Specialist
Specialist

If you have more number of start date end date pairs, then I would recommend what Colin_Albert suggested.

Highlighted
Creator
Creator

Thank you Harshal for this helpful answer, I will try this.

Highlighted
Creator
Creator

Thank you Colin for this helpful answer, I will try this.

Highlighted
Creator
Creator

Hello Colin_Albert,

I'm stuck in the script, can you please give me a sample load script for your solution?

thank you in advance and more power!

BR,

jason

Highlighted

I would use a for-each loop, which will auto-concatenate the data to a single Stage table.

for each Stage in 'A', 'B','C'

     trace Loading Data for Stage $(Stage) ;

   

     Stage:

     Load

          ProjectID,

          ProjectID & '|' & $(Stage) as ProjectStageKey,

          '$(Stage)' as Stage,

          $(Stage)_Start_Time as Start_Time,
          $(Stage)_End_Time as End_Time,

          Dimension_$(Stage) as Dimension,

          ... other fields

    from <stage data table>

     where $(Stage)_Start_Time > ''

next Stage ;