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
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)
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)
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)
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