How to display 30 Minutes time range in Straight table?

[whitestonek]

How to display 30 Minutes time range in Straight table?

I have three Columns in this below table. One Column having DATE with TIME, and another columns are Pieces and Shipment Number.

Date&Time	Piece	ShipmentNumber
05-03-2018 9:10:00	5	01625059683P
05-03-2018 12:30:00	12	01625071970P
06-03-2018 7:25:00	24	01625077802P
05-03-2018 6:08:00	44	01625094193P
07-03-2018 3:15:00	3	02042855341P
07-03-2018 2:09:00	35	02042855352P
07-03-2018 10:18:00	55	02069121780P
06-03-2018 4:28:00	76	02069121916P
06-03-2018 5:30:00	32	02069130504P
05-03-2018 0:00:00	65	02069130526P

So, when I select on particular DATE calender (Eg: 05-03-2018) in filter, the data should display in straight table below like this with 30 minutes time range.

OUTPUT:

Time               Pieces

01:00  01:30     15

01:31  01:59     15

02:00  02:30       0

02:31   02:59     08

03:00   03:30      0

03:31   03:59      0

04:00   04:30    33

04:31   04:59    89

[tresesco]

Are you looking for something like:

If this output looks good for your sample data, you could try using class()

[ogautier62]

Hi,

it should be this :

as dimension :

day,

floor((hour([Date&Time])*60 + Minute([Date&Time])) / 30)      (you can hide)

as expression

bucket beginning :

if(mod(floor(min(floor((hour([Date&Time])*60 + Minute([Date&Time])) / 30))/2),2)=0,

     floor(min(floor(hour([Date&Time])*60 + Minute([Date&Time]) ) / 30) /2)  & ':00',

     floor(min(floor(hour([Date&Time])*60 + Minute([Date&Time]) ) / 30) /2) & ':31'

    )

bucket and :

if(mod(floor(min(floor((hour([Date&Time])*60 + Minute([Date&Time])) / 30))/2),2)=1,

     floor(min(floor(hour([Date&Time])*60 + Minute([Date&Time]) ) / 30) /2)  & ':59',

     floor(min(floor(hour([Date&Time])*60 + Minute([Date&Time]) ) / 30) /2) & ':30'

    )

regards

[whitestonek]

Thank you Tresesco,

Can you please send that script.

[whitestonek]

Thank you Tresesco,

Can you please send that script.

[tresesco]

Here its is:

Load *,

      Dual(Time(SubField(ClassTime, '<= x <',1),'hh:mm')& ' - '&Time(SubField(ClassTime, '<= x <',2),'hh:mm'),Frac("Date&Time")) as ClassTime2;

Load *,

    Class("Date&Time",1/24/2) as ClassTime

[ogautier62]

very nice and elegant

[whitestonek]

Hi Tresesco, I seen your script.But here i am confusing one thing is those 2 scripts i need to apply in script editor or in expressions or what?I want to display data in straight table.I want to see output like below by using calender object as a filer.

OUTPUT:

Time               Pieces

01:00  01:30     15

01:31  01:59     15

02:00  02:30       0

02:31   02:59     08

03:00   03:30      0

03:31   03:59      0

04:00   04:30    33

04:31   04:59    89

[tresesco]

PFA