Get Excel Data based on Unique Rows Value

Help Studio
, ,
1

Hello,

I have an excel file which has column “CustomerNo” and it contains a String and Double Value. I have to get all the detail of this customer.

For Example: First I will select 1 customer,

  1. Customer Name: “AB0001”.
  2. Keep all the records of this customer.
  3. Based on this record get the Sum of Cash.
  4. Based on this record get the Min days
  5. Write this data(which we get from Point 2) to another excel file.
  6. Now repeat for the next customer and do step (1-5)

Below img is just an overview:

mainQ
mainQ1365×703 104 KB

So I am stuck doing this as the CustomerNo has string n double value.
Any help on this?

Regards,
Geet M

2

@iamgeet
So you want to aggregate the CustomerNo Rows for each group and find the MIn of Days And Sum of Cash?

In general this will be done with a Group by using a custom component from e.g Go or a Custom LINQ statement.

If LiNQ should be reduced and you want to keep the control you can do it with following technique:

Build the distinct CustomerNos with: YourDataTableVar.AsEnumerable.Select(Function ( r ) r(CustomerColIdxOrName).toStrin.Trim).Distinct.ToArray - returning a String array e.g Customers

Then with a for each iterate over Customers and use the value within a Filter Data Table

On the filtered row result Min, Sum can done with:
dtFilteredVar.AsEnumerable.Min(function (r) Cint(r(ColIndexOrName).toString.Trim)
dtFilteredVar.AsEnumerable.Sum(function (r) Cint(r(ColIndexOrName).toString.Trim)

with the index out from for each you can build the counting Number of the Customers

Is it possible that you can share sample excel file?

5

@iamgeet
I will have a look on it later and will come back to you

6

Sure, no worries and Thank you once again :smiley:

7

@iamgeet
find a starter help here:
iamgeet.xaml (10.7 KB)

For a next step I would suggest play and explore in a seperate XAML the functionalities like conversion to double, filter datatable …

This will give you practice and experience so you are ready for your next implementations

4 Likes
8

Sure, Thanks !!!

9

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