How to separate data sets in one table

[Jiao Chen]

Hi, I have a data set thatrecords multipleexperiments progress like below. It only recorded progress percentage and time. I would like to add a third column ID as shown below so I can separate different experiment set. Is there a way that I can do it Thank you very much.

 

 

 

Progress

Time

 

 

0.01

2:18:01PM

 

 

0.02

1:18:01PM

 

 

 

 

 

 

99.99

4:17:59PM

 

 

0.01

4:18:03PM

 

 

0.02

4.18:04PM

 

 

 

 

 

 

99.99

6:18:02PM

 

 

 

 

 

 

Progress

Time

ID

 

 

0.01

2:18:01PM

A

 

 

0.02

1:18:01PM

A

 

 

 

 

A

 

 

99.99

4:17:59PM

A

 

 

0.01

4:18:03PM

B

 

 

0.02

4.18:04PM

B

 

 

 

 

B

 

 

99.99

6:18:02PM

B

[Leo Kwok]

Please kindly find the dxp file and Excel file for the separation of table.

First, navigate to the Data Canvas.

Then, create a new column and typeCEILING(ROWID() / 9999) as the Expression.

The results will be show as capture screens, 1 represents the first table, 2 represents the second table, etc.

Hope it can help!

[Fabian Duerr]

The question is, how do you know that one experiment endsand another begins

This previous suggested solution will work only as long as you justhave two experimentsand only if you are sure, that the first experiment has exactly 9999 rows. A more general approch would be to split the data by the time column if start and end time of the experiments are known. Then you could work with a case() function comparing if the recorded time falls within start and end times.

You could also add one calculated column that checks the progress value. Because as soon as the progress in not ascending (dropping back to 0) you know that a new experiment starts and the current ends. This requires that the internal order of your table is correct. Which seems to be the case for your example. Here, you can make use of the lag() or lead() function.