How to make this Linq Query Dynamic

Help Studio
, ,
1

I have a file where im grouping the datatable by col ‘Country’ , here is the input file (also contains output)
PendingData.xlsx (69.9 KB)

i was getting 4 values before now i want to accept all the values from the Col ‘Designation’ and make as many col in the output file

Here is the Linq i was using

( From r In in_InputFile
Group By z=r("Country").ToString.Trim Into grp = Group
Let temp1 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("POR")).Count
Let temp2 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("ABC")).Count
Let temp3 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("wxy")).Count
Let temp4 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("DEF")).Count
Let newRows = New Object() { grp(0)("Country").ToString, temp1.ToString, temp2.ToString, temp3.ToString, temp4.ToString, temp1+temp2+temp3+temp4}
Select dt_Output.Rows.Add(newRows)).CopyToDataTable

i was getting count of Desgnation that are for each country , but the designation was fixed before there were only four of them so i add only 4 , now the requirment is to accept as many designation as it has

2

we should keep in mind that

will also rely on a in advanced defined structure

We would recommend to create at least a generic intermediate output, which we can handle dynamic.

Country Designation Count
Belarus POR 13
Belarus … …
… … …
…

And using this afterwards to create the output, when this is really needed and the above (Country, Desifnation, Count) structure cannot be used

3

Reacting also on your other topic get presented a small prototype:

Variables:
grafik

Flow overview:

grafik
grafik308×838 50.9 KB

Preparing Intermediate Data:
grafik

dtIntermediate =

(From d In dtData.AsEnumerable()
Group d By k1=d("Country").toString.ToUpper.Trim, k2=d("Designation").toString.ToUpper.Trim Into grp=Group
Order By k1,k2
Let ra = New Object(){k1,k2,grp.Count}
Select r = dtIntermediate.Rows.Add(ra)).CopyToDataTable

Setup Final Report Structure

grafik
grafik630×666 53.7 KB

  • set the country column
  • set dynamic all distinct designations
  • set the total count column

Final Report
dtReport =

(From d In dtIntermediate.AsEnumerable
Group d By k1=d("Country").toString.Trim Into grp=Group
Let dict = grp.ToDictionary(Function (lk) lk("Designation").toString, Function (lk) Convert.ToInt32(lk("Count")))
Let des = Designations.Select(Function (x) If(dict.ContainsKey(x), dict(x), 0))
Let cnt = des.Sum(Function (x) x)
Let ra = des.Cast(Of Object).Prepend(k1).Append(cnt).toArray
Select r = dtReport.Rows.Add(ra)).CopyToDataTable

So from your input Excel ( we filled up empty country columns with a dummy value we get:

Immediate Report
grafik

Final Report
grafik

We also would have the option to limit the column by a subset as we incorporated the Designations list, which we can also use as a dynamic Designation Filter, configurable outside e.g. Config File

Find starter help here:
ppr_IntermediateGrouping_ReportGeneration.xaml (16.4 KB)

4

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