Optimize script

[Qliksense_77]

Hi,

I have a large set of data. I was wondering if my script could be optimized. 

I have some measures that are defined by the starttime. Other measures are defined by the endtime. The below example illustrates my script. It works fine, but it doubles the initial number of rows. 

I need to show both types of measures at the same time with same calendar. Therefore I have a master calendar, that I connect to the field Calendar_datetime.

Any suggestions to how I can limit the number of rows? 

Data:
Load * INLINE [
Id, starttime, endtime, Count
1, 02/03/2020 13, 05/03/2020 14, 1
2, 05/03/2020 14, 05/03/2021 15, 1
];

Data2:
Load
starttime as Calendar_datetime,
Count as Number_of_start
Resident Data;

concatenate(Data2)
Load
endtime as Calendar_datetime,
Count as Number_of_end
Resident Data;

Drop table Data;

Result:

Calendar_datetime	Number_of_start	Number_of_end
02/03/2020 13	1	-
05/03/2020 14	1	-
05/03/2020 14	-	1
05/03/2021 15	-	1

Desired result:

Calendar_datetime	Number_of_start	Number_of_end
02/03/2020 13	1	-
05/03/2020 14	1	1
05/03/2021 15	-	1

[Kushal_Chawda · Accepted solution]

@Qliksense_77  you might need to create a link table with common date filter

Data:
Load Id,
     starttime,
     endtime,
     starttime &'|'& endtime as Key ,
     Count
FROM table;

Link:
LOAD FieldValue('Key',RecNo()) as Key,
     SubField(FieldValue('Key',RecNo()),'|',1) as Calendar_datetime,
     'start_time' as datetime_flag
AutoGenerate FieldValueCount('Key');

LOAD FieldValue('Key',RecNo()) as Key,
     SubField(FieldValue('Key',RecNo()),'|',2) as Calendar_datetime,
     'end_time' as datetime_flag
AutoGenerate FieldValueCount('Key')

Autonumber Key; 

You can now link Calendar_datetime field from link table to your Calendar. You can then use datetime_flag in your measure to represent it correspond to start and end time 

sum({<datetime_flag={'start_time'}>}Count) // Measure with start time
sum({<datetime_flag={'end_time'}>}Count)    // measure with end time

[Qliksense_77]

@Kushal_Chawda , thank you. Nice to learn new stuff 🙂