Sorting a DataTable by 2 column, when 1 condition is more important then the second one

Hi everyone

I have a datatable that I took from an Excel file and I filtered it.
And now I want to sort it out, but by 2 columns (2 conditions)!
condition 1 is by “Status” (I want a specific value to be first), and then the second condition (AFTER THE FIRST SORTING) will be by “Date” where the first value is the closest to the current date

Thank u all for everything

can you provide a sample excel

Config - Linoy.xlsx (20.6 KB)

Hi @dlichten,

I’ll suggest you to use following LinQ query in Assign activity

FilteredDT = (From x In DT.AsEnumerable() Order By convert.ToInt16(x("Status")), DateTime.ParseExact(x("Date").ToString, “dd.MM.yyyy” , System.Globalization.CultureInfo.InvariantCulture) Select x).CopyToDataTable

Note —> Make sure that all the dates in excel sheet should in one format
(As you have in dd.MM.yyyy format, So like wise,
2.2.2020 should be 02.02.2020
10.2.2020 should be 10.02.2020)

So Excel data should be
image
—> Output image

Didnt work
this is my assign “Sorted” TO “(From x In FilteredDT.AsEnumerable() Order By convert.ToInt16(x(“Status”)), DateTime.ParseExact(x(“Date”).ToString, “dd.MM.yyyy” , System.Globalization.CultureInfo.InvariantCulture) Select x).CopyToDataTable” (without the quotes)

and it gave me this error Assign: Input string was not in a correct format.
in the Excel I changed all the dates to be dd.mm.yyyy (by hand couldnt find the automatic way in excel)

First I used Filter DataTable to drop out rows with “Status”=3
and then I entered your assign…

this output is perfect
I’ll watch what u did
but now I think that my Status is not by numbers its by words (it was hebrew so I change to numbers)
how can I do the same - if I have in “Status” = “Urgent”," Semi_Urgent", “Regular/or others” (different names but same order)

No worries, If you have words (not numbers) then, using Order by does arrange them in alphabetical order.
you need to convert that value in string format, one change in query convert.ToString(x(“Status”)) then your whole query will be,

(From x In DT.AsEnumerable() Order By convert.ToString(x("Status")), DateTime.ParseExact(x("Date").ToString, “dd.MM.yyyy” , System.Globalization.CultureInfo.InvariantCulture) Select x).CopyToDataTable

Thanks that what I changed in it
but if I dont want to sort it alphabetically?I want it to be sorted by urgent lvl…
first = “Urgent” (in hebrew at may not be first…)
second = “Semi_Urgent” (in hebrew at may not be second…)
only those two the rest is deleted for now… (and if not deleted to be the last after the first and second)

thanks again it still was very helpful

For this you can use array var and store Urgent & Semi_Urgent in it —> {“Urgent”,“Semi_Urgent”}
Use for each to iterate through that array var, and use Filter Data Table in that for each to filter datatable with value from array var one by one.
Then use assign with previous linq query to sort dates (on filtered datatable var i.e output var of filter datatable).

(From x In DT.AsEnumerable() Order By DateTime.ParseExact(x("Date").ToString, “dd.MM.yyyy” , System.Globalization.CultureInfo.InvariantCulture) Select x).CopyToDataTable

(This time, exclude status part from query.)

Now, use append range activity to append data in excel sheet for evrey iteration ( Urgent & Semi_Urgent)
that’s it :slight_smile:

can u send me an xaml file?
and also a sorted DT, if I do another sort on it - will it destroy the last sort or build on it?

Hey @dlichten,

Check this Sorting.zip (53.2 KB)

image

nice!!
loved the solution!
elegant!!!

thank u very much

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.