Fillup/Group By - Question About FillUp Example Xaml file by PPR

Help Studio
, ,
1

Greetings,

I am hoping PPR will chime in here!

I am needing to Fillup empty cells in a datatable so I can do Groupby - PPR has shared a Fillup example file ( FillUp_BlockBlanks.xaml (8.7 KB)) in a few threads, and my question is:

How would I save the filled up result as a datatable?

In the example file, it is being saved as a dictionary.

grafik

Does it need to be saved as a dictionary? Or can it be done as a datatable?

I can follow the Fillup example fine, just confused on the dictionary part.

I would prefer it to be saved as a “filled up” datatable, so I could then do the Groupby method on it.

Thank you for the superb assistance, PPR!

2

@Sc100
Thanks for your request

we will take it as a trigger to hand over Fillup case by a tutorial to the community. One of my work fellows @kumar.varun2 did also work out an approach when the fillup value is at the end of the segment. Maybe we will find a combined one.

Let’s start with the fillup:
a given data sample
grafik
is to be filled up with the values from Column1 to prepare the data e.g. for a grouping. We do see this scenario often, when Excel was setup with merged cells for the groups

The result after fillup:
grafik

then we are prepared to take this data for the grouping

the result of the fill up is the filled up datatable

Refering to the question of how to create a data table for the groups. We would recommend to define the following:

  • the target datatable structure
  • the aggregation definition for the group members

Let’s assume the above example will use String Join to flatten the string of the group member col(1) values. We can do:

dtTarget = Col1, Col2

(From d in dtData.AsEnumerable
Group d by k=d(0).toString into grp=Group
Let v = grp.Select(Function (x) x(1).toString).toArray
Let fs = String.Join(",", v)
Let ra = new Object(){k, fs}
Select r = dtTarget.Rows.add(ra)).CopyToDataTable

so in short:

  • fillup is returning a datatable
  • grouping result we set up as needed