topic Re: Many Start-End Times columns in a Table in QlikView

Many Start-End Times columns in a Table

jasonseril — Tue, 24 Feb 2015 06:43:23 GMT

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

Re: Many Start-End Times columns in a Table

AbhijitBansode — Tue, 24 Feb 2015 06:59:46 GMT

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

Re: Many Start-End Times columns in a Table

jasonseril — Tue, 24 Feb 2015 07:04:47 GMT

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

Re: Many Start-End Times columns in a Table

Tue, 24 Feb 2015 07:20:38 GMT

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..

Re: Many Start-End Times columns in a Table

Colin-Albert — Tue, 24 Feb 2015 07:50:28 GMT

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.

Re: Many Start-End Times columns in a Table

AbhijitBansode — Tue, 24 Feb 2015 08:56:44 GMT

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

Re: Many Start-End Times columns in a Table

jasonseril — Tue, 24 Feb 2015 09:23:38 GMT

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

Re: Many Start-End Times columns in a Table

jasonseril — Tue, 24 Feb 2015 09:24:31 GMT

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

Re: Many Start-End Times columns in a Table

jasonseril — Wed, 25 Feb 2015 02:05:17 GMT

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

Re: Many Start-End Times columns in a Table

Colin-Albert — Wed, 25 Feb 2015 07:45:15 GMT

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 ;