Problem with interval match in time line pivot table

[#1]

Hi All

I found this attached application in the forum which uses interval match function in the script. Problem with this is it is not working as expected. Few rows has been ignored may be becuase of the following reason.

1. Rows with StartDate > EndDate are ignored.(This could be because of mistake in the data)

2. Rows with StartDate = EndDate are ignored.

3. Apart from that there are few others rows which are missing in the chart when compared to the original data which I couldn't find it out yet.

Can some one help me out on this please so that it works as expected.

Hope to receive some positive response soon.

[pover]

Attitude,

1. You'll need to review the data for case 1 since intervalmatch will not work in this case. You could make StartDate equal to EndDate when StartDate is greater than EndDate, but the data is wrong.

2. All your dates have a .308 decimal when you convert them to numbers because you generate them from a timestamp so when StartDate = EndDate the timestamp was to be exactly at 7:23:31 to be considered in the range. For your intervalmatch use all fields with a date format or create them using the function date(floor([TimestampField])) where the floor with get rid of the decimal since the date function only changes the format.

3. A few cases might exist of timestamps before 7:23:31 that would not be considered in the range for the same date so do the same fix as point 2 and you might fix the other cases.

Regards.

[#3]

Karl Pover wrote:
1. You'll need to review the data for case 1 since intervalmatch will not work in this case. You could make StartDate equal to EndDate when StartDate is greater than EndDate, but the data is wrong. <div></div>

Yes I need to think something like that when StartDate=EndDate

Karl Pover wrote:
2. All your dates have a .308 decimal when you convert them to numbers because you generate them from a timestamp so when StartDate = EndDate the timestamp was to be exactly at 7:23:31 to be considered in the range. For your intervalmatch use all fields with a date format or create them using the function date(floor([TimestampField])) where the floor with get rid of the decimal since the date function only changes the format. <div></div>

Based on your request I did the following changes in the script and I hope this is the change which you have asked for. If not please do let me know.

Also I think after doing this change it is working as expected I guess. Will attach the application later on as the internet is very slow now. If I have exact change which you are looking for and if it is working fine then it would be great.

date(floor(A15)) as StartDate,
date(floor(A16)) as EndDate,

Karl Pover wrote:
3. A few cases might exist of timestamps before 7:23:31 that would not be considered in the range for the same date so do the same fix as point 2 and you might fix the other cases. <div></div>

I didn't get what do you mean here! Could you please explain it to me please.

Once the internet connection is fine I will send you the application soon. I am extremely sorry from my side for replying you so late. I think if this works fine then this is the solution which I was looking for. Infact I have been looking this from long long time! 🙂 Will be much happy if it works fine!

[#4]

Hi Karl

I am also requesting you suggest something to removing the synthetic keys which are formed in the application. I don't know how to remove the synthetic key as it may have some performance issue in the future. As the synthetic keys are formed by StartDate and EndDate it is little confusing to remove the same.

Please do reply me when you find free time.