Create a sorted partitioned table based on consecutive values

[Katerina_Chabova1 (Katerina Chabova)]

Hello,

I have DT like this

Number
6000623077
6000623462
6000623463
6000623464
6000623465
6000623466
6000623674
6000623675

And I need create a new DT from the old one with two columns From, To Which would skip consecutive values and extract only the lowest and highest. Like below

From	To
6000623077	6000623077
6000623462	6000623466
6000623674	6000623675

Do you have any idea hot to do that?
Thank you

[supermanPunch (Arpan)]

@Katerina_Chabova1

a new DT from the old one with two columns From, To Which would skip consecutive values and extract only the lowest and highest. Like below

Hi @Katerina_Chabova1 ,

We would require more details on how the Output is derived, What was the Logic used, How would you do it manually ? Why is the First value repeated on both the columns ?

If you could help us in providing more information on the logic/condition to be used, we may be able to help you in providing appropriate suggestions.

[Katerina_Chabova1 (Katerina Chabova)]

Hi, the values are invoice number and I need it to insert into SAP, so far it is uploading one by one, but I would like to modify it so we can upload multiple invoices in one time.

[supermanPunch (Arpan)]

@Katerina_Chabova1

I need it to insert into SAP, so far it is uploading one by one, but I would like to modify it so we can upload multiple invoices in one time.

@Katerina_Chabova1 ,

Could you also show us the SAP Screen where this is to be input ?

@Katerina_Chabova1

From	To
6000623077	6000623077
6000623462	6000623466
6000623674	6000623675

Do Note that this Output format is still not explained in a way we could understand the logic. Maybe a relation / pattern is shown to use but that relation conflicts certain things and is not completely clear.

[Katerina_Chabova1 (Katerina Chabova)]

The SAP screen

The logic is, we create a invoices during a month and at the end of month we need to pair it to payment and one of the step is input the data into the SAP table.

[Yoichi]

Hi,

Hope the following sample helps you.

listValue = dt.AsEnumerable.Select(Function(r) Int64.Parse(r("number").ToString)).ToArray
arrFrom = listValue.Where(Function(i) not listValue.Contains(i-1)).ToArray
arrTo = listValue.Where(Function(i) not listValue.Contains(i+1)).ToArray

Then

dtResult = arrFrom.Zip(arrTo,Function(x,y) dtResult.LoadDataRow({x,y},False)).CopyToDataTable()

Sequence.xaml (11.9 KB)

Regards,

[Katerina_Chabova1 (Katerina Chabova)]

Hi, this is given me error arithmetic operation resulted in an overflow. And first i needed to convert the row.item to Cint

[Yoichi]

Hi,

Int32 (Cint) supports from -2,147,483,648 to 2,147,483,647. So, 6000623077 will be overflow.
Please use Int64 ( or String if you can accept non-numeric type)

Regards,

[Katerina_Chabova1 (Katerina Chabova)]

Thank you, it is working

[ppr (Peter)]

@Katerina_Chabova1
Welcome to the forum

We do like and use also the Zip approach from @Yoichi for such tasks as it is a compact way to model it.

Let us present an alternate approach which done on a dictionary base and offers some debuging / tracing options

from data:

we can create a dictionary:

Can use it directly for entering or create the result data table as mentioned above by you:

Variables:

Creating Dictionary:

Creating result DataTable:

All LINQs used within approach can be decomposed to essential activities when it is needed.

Kindly note: CLng (a conversion to Long / Int64), Int64 is used to handle the value ranges

Find starter help here:
MakeFromToSegments_DT_1Col_ConsecutiveNbrs.xaml (13.1 KB)

Feel free to explore this alternate for learning purposes as well