Extracting relatable data from a Data table

Hello all,

I have a Data table with Column A and B for example, Column A has Date values and Column B has some numeric values. The case is where, In Date Column some date can be repeated like i displayed below, “I Need only the Value of Last occurrence of a Particular date” , With the below diagram, I need the last 3rd Jan 2020’s value and last 4th Jan 2020’s Value.
image

Thanks in advance !!

1 Like

Hi @Rakesh_Sampath you can check the workflow for reference

It will help

excel.zip (54.7 KB)

Regards

Nived N

Happy Automation

2 Likes

If the data table has many columns, and we just have to take the values from the correct date , what modifications should be done to this workflow sir !? @NIVED_NAMBIAR

Thanks in advance !!

From the data table like this, for example.

Hi @Rakesh_Sampath

Did u mean that u need to do the same operation when there are multiple columns ?

Yeah sir, exacly !! i have attached a data table screen shot above in my reply sir !!

What value u need to extract here from the excel

With the last occurrence of a particular date i need to extract Balance Column’s value

in the message box to display value as in Workflow I had shared

I had used

dt2.Rows(dt2.Rows.Count-1)(1).ToString

So instead of that use the below to display the balance column of last occurance date

dt2.Rows(dt2.Rows.Count-1)(“Balance”).ToString

Try this way bro @Rakesh_Sampath

Hope it helps

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed::relaxed:

With the alternate expression you provided returns the last value of Balance data bro !! its not returning the all date’s last occurrences’s value. @NIVED_NAMBIAR

But it is return latest occurrence na ?

No bro :frowning: with the alternate expression you provided , it returns 16,161.34 (The last value of balance column) it’s not returning the 02-Jan-2020’s last occurrence’s balance value - 11,161.34. Better understanding with Screenshot i given above bro !! @NIVED_NAMBIAR

Thanks in advance !!

Hi @Rakesh_Sampath

Hope its Resolved
Have a nice day

Kindly let know for any queries or clarifications
rakesh.zip (9.6 KB)

1 Like

By any chance, you want to get the sum of the value column as well?
like below -

image

No sir, Need every Date’s Last occurrence’s Balance’s column value. !!

Verify the screenshot i posted below, where it contains many columns, where we just need Balance value for every date’s last occurrence

So in this case you want you want to get record marked in red?

@ppr can we have you inputs please?

Exactly sir !! For every date it should return it’s balance column’s value , if a date is repeated like 2-jan-2020 three times, it should take last occurrence of date’s “Balance Column Value”(i.e, third 2-jan-2020)

1 Like

I have a solution but again we have to maintain the date format which is not coming correct but its picking the correct data -

Can you give it a try and let us know?

(From d In DT2
Group d By k=d("Date").ToString.Trim Into grp=Group
Select grp.Last()).CopyToDatatable

I am looking for input from experts like @Palaniyappan @NIVED_NAMBIAR and @ppr , to correct my wrong or some other solution.

3 Likes

Perfect @indrajit.shah

2 Likes

Thanks sir !!!

1 Like