Excel Activity - How to Filter the duplicate rows and add in a variable or save it in another datatable

Help Studio
1

I have an Input excel file from which I have to filter the unique combination of 4 columns
example: Sample 2
Columns: Item code, discount, date and location
This is what required see in this below picture:
Sample1_LI
First check the Item code then Discount then Date and finally location total 6 combinations I want. better if I can save in 6 diff variables otherwise please give an suggestion to save these 6 combinations.
Please help me with a solution
Thanks in advance!

2

@ppr @SamanGuruge @bcorrea an you please help me in the above asked question
Thanks in advance!

3

@sushmithakiran02
it can be done with a group by on Item code, Discount, Date
a group can be returned as a list(of Datarow)
All groups can be returned as a List(Of List(DataRow))

4

Hi @sushmithakiran02,

Can you explain the ultimately what you are going to do using this result you mentioned

it can be done using Linq Query and you will be out 6 DT s

(From r In DT.Select() WHERE r(“Discount”).ToString.Contains(“5%”) AND r(“Location”).ToString.Contains(“5%”) AND r(“Date”).ToString.Contains(“28-07-2020”) SELECT r ).CopyToDataTable

This can be Contain function or Equal

2 Likes
5

@sushmithakiran02
find starter help here:
GroupBy_3ColKey_WithDate.xaml (12.0 KB)

Currently the date col is treated as string. Maybe you will adopt to proper datetime Parsing (DateTime.ParseExact) and enhance it with this

3 Likes
6

I tried this but unable to write the same to a datatable

7

Its a dynamic data table ‘.contains’ is not possible.They are the purchase items for which I need to generate an invoice

  1. filter the location - for example I have 5 locations then 5 diff invoice will get created.
  2. for those 5 locations filter the item code - if there are multiple item codes for these 5 locations then further more multiple invoices need to be created.
  3. Third step check date - In case for the same date, same location but item code is diff then a different invoice has to be created.
  4. Final check Discount - even though all three Date, Location and Item code matches and just Discount is different we have to create a separate invoice.

Thanks for ur response!

8

no let it write to dtresult as in the example

1 Like
9

amazing the logic worked I have built it with real data.
But there is an issue it is not able to group the unique values together
Thank you somuch for ur efforts :smiley:

10

@sushmithakiran02
when it is working , perfect. so just indicate it with closing the topic by flagging the solving post as solution.

when the groups are not correctly recognized:
hust share some sample (maybe anonymised) sample data with us and we will have a look on it. Focus on the data col for analysis maybe the time part is confusing as it is fractioning the dates

11

The logic which you have shared worked but it is not solving my problem.

12

then the next step is to map requirements and solution and breaking down what is solved, what not and to describe the differences.

The logic which you have shared worked but it is not solving my problem.

a feedback like above is not processable for us as we cannot derive facts from it

13

Just tell me one thing please from where did you get those variables d and k in the above mentioned solution?

14

d, k1, k2… are like variable referencing particular items
From d In … d lets reference the row that is currently looped

have a look also here:

15

Yes, but you haven’t declared these variables anywhere?

16

no, only in the statement it has to be correct named and used. As you can refer to my XAML it was not declared in the variable section (But we can use variables as well)

Just have a look on the provided link it gives an introduction