How to increment variable position in an array at the time of the creation

[indiedev91]

in this linq query how to increment the position of temp0.ToString in the array

dt_output = ( From r In in_InputFile
Group By z=r("Country").ToString.Trim Into grp = Group
Let temp0 = grp.where( Function(x) x("Designation").ToString.Trim.Equals(currentItem)).Count
Let newRows = New Object() {grp(0)("Country").ToString,temp0.ToString}
Select dt_Output.Rows.Add(newRows)).CopyToDataTable

the scenario is that as the temp0.ToString is set at index 1, it is overwriting the values that is already there

so my idea is to give it a position based on a counter

this code is already in a loop and has a counter variable called Count, the currentitem as the var that is holding the value inside the .equals(currentitem)

The previous scenario includes static values so the previous linq was like this

( From r In in_InputFile
Group By z=r("Country").ToString.Trim Into grp = Group
Let temp0 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("ABC")).Count
Let temp1 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("POR")).Count
Let temp2 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("PQR")).Count
Let temp3 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("DEF")).Count
Let temp4 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("wxy")).Count
Let temp5 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("Avr")).Count
Let temp6 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("ABc")).Count
Let temp7 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("MNP")).Count
Let temp8 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("GHI")).Count
Let temp9 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("xyz")).Count
Let temp10 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("FHI")).Count
Let temp11 = grp.where( Function(x) x("Designation").ToString.Trim.Equals("STV")).Count
Let newRows = New Object() { grp(0)("Country").ToString,temp0.ToString,temp1.ToString,temp2.ToString,temp3.ToString,temp4.ToString,temp5.ToString,temp6.ToString,temp7.ToString,temp8.ToString,temp9.ToString,temp10.ToString,temp11.ToString}
Select dt_Output.Rows.Add(newRows)).CopyToDataTable

as u guessed the currentitem is holding the value that is used in

.Equals()

Input and Expected Output

[Anil_G (Anil Gorthi)]

@indiedev91

Please try this

dt_output  = ( From r In in_InputFile
Group By z=r("Country").ToString.Trim Into grp = Group
Let temp0 = grp.where( Function(x) x("Designation").ToString.Trim.Equals(currentItem)).Count
Let newRows = New Object() {grp(0)("Country").ToString,"","","","","","","","","",""}
Select dt_output.Rows.Add(newRows.Select(Function(x,i) If(i=currentItem,temp0.ToString,x)).ToArray)).CopyToDataTable

As the table contains the maximum number of columns…add the array row with all possible empty values…and depending on index or currentItem replace the actual value in array

cheers

[indiedev91]

@Anil_G

Let newRows = New Object() {grp(0)("Country").ToString,"","","","","","","","","",""}

can you explain why you have given static empty " " ? , The number column’s to be made is dynamic and dosent have a fix value or number of col

[Anil_G (Anil Gorthi)]

@indiedev91

1. First you need to know how many columns of data you need to write else you cannot create a dynamic number of rows…if you do so it would throw error sayign array is larger or smaller than the required array

Even if it is dynamic…you need to create a logic to define the array size as the array size should exactly match with the datatble column length

And after defining you can replace the actual value using the index of the array and then write

Or create empty array dynamically and then replace all values you need New Object[length]

Cheers

[indiedev91]

cant we use list variable and append/create new columns for each iteration ?

[Palaniyappan (Forum Leader)]

Try using like this

Assign

Count As Integer = 0 ’ Initialize your counter variable

Then

dt_output = (From r In in_InputFile
              Group By z = r("Country").ToString.Trim Into grp = Group
              Let newRows = New Object() {grp(0)("Country").ToString}.Concat(tempValues.Skip(Count).Take(1).Select(Function(temp) temp.ToString)).ToArray()
              Let Count = Count + 1 ' Increment the counter
              Select dt_Output.Rows.Add(newRows)).CopyToDataTable

Cheers @indiedev91

[Anil_G (Anil Gorthi)]

@indiedev91

yes you can create…pass the list as inout inside instead of creating for each loop as the list length or the array length will be fixed for all the rows…

and the given select query below can be used to add the values in the required inded palces

Hope this helps

cheers

[indiedev91]

is this Correct ?

( From r In in_InputFile
Group By z=r("Country").ToString.Trim Into grp = Group
Let temp = grp.where( Function(x) x("Designation").ToString.Trim.Equals(currentItem)).Count
Let newRows = New Object() {grp(0)("Country").ToString}.Concat(tempValues.Skip(Count).Take(1).Select(Function(temp) temp.ToString)).ToArray()
Let LinqCount  = LinqCount + 1 ' Increment the counter
Select dt_Output.Rows.Add(newRows)).CopyToDataTable

[indiedev91]

Im sorry for asking duplicate question about the same topic , but i want a answer to this problem and i really dont understand linq , im still learning (actually just started ), so thats why maybe if i ask any dumb question regarding linq, please forgive me

and thank you so much for the people answering my question and helping me out

@Anil_G
@Palaniyappan
@ppr
and many more thank you for the solutions you provided , not just to this thread but too all of my previous threads , really appreciated

[Anil_G (Anil Gorthi)]

@indiedev91

Can you elaborate what you want to do …may be then we cna help better on how to acheive it

Cheers

[indiedev91]

The process is to group each country and get counts of each designatioin that is assigned to each country

for eg
Input

Country      Designation
USA               ABC
INDIA             QAZ
USA               ABC
USA               OBS
CANADA            ABC
USA               OPM
INDIA             LLM
INDIA             QAZ
INDIA             ABC

Ouput

Country           ABC         QAZ         OBS       OPM       LLM

USA                2           0           1         1         0
CANADA             1           0           0         0         0
INDIA              1           2           0         0         1

PendingData.xlsx (69.9 KB)

[Anil_G (Anil Gorthi)]

@indiedev91

Better to use pivot table…

You have a create pivot table activity

Cheers

[Palaniyappan (Forum Leader)]

@indiedev91

this Correct ?

Hmm this would work I believe

(From r In in_InputFile Group By z = r("Country").ToString.Trim Into grp = Group Let temp = grp.Where(Function(x) x("Designation").ToString.Trim.Equals(currentItem)).Count LetnewRows = New Object() {grp(0)("Country").ToString}.Concat(tempValues.Skip(Count).Take(1).Select(Function(temp) temp.ToString)).ToArray() Let LinqCount = LinqCount + 1 ' Increment the counter Selectdt_Output.Rows.Add(newRows)).CopyToDataTable

The rest of the query appears to be logically sound, and it should work as intended, incrementing the LinqCount variable and concatenating the temp values into the newRows array for each group in your input data.

Hope this clarifies
@indiedev91

[Palaniyappan (Forum Leader)]

For this

@indiedev91

The process is to group each country and get counts of each designatioin that is assigned to each country

LINQ query to do that:

datatable = (From row In YourDataTable.AsEnumerable()
 Group By Country = row("Country").ToString() Into Group
 Let ABCCount = Group.Count(Function(r) r("Designation").ToString() = "ABC")
 Let QAZCount = Group.Count(Function(r) r("Designation").ToString() = "QAZ")
 Let OBSCount = Group.Count(Function(r) r("Designation").ToString() = "OBS")
 Let OPMCount = Group.Count(Function(r) r("Designation").ToString() = "OPM")
 Let LLMCount = Group.Count(Function(r) r("Designation").ToString() = "LLM")
 Select YourDataTable.NewRow().ItemArray = {Country, ABCCount, QAZCount, OBSCount, OPMCount, LLMCount}
).CopyToDataTable()

Here we group the rows by Countryand within each group, we use Count and Function to count the occurrences of each Designation (“ABC,” “QAZ,” “OBS,” “OPM,” “LLM”).

If u don’t the designation and keep it dynamically

datatable =
(From row In YourDataTable.AsEnumerable()
 Group By Country = row("Country").ToString() Into Group
 Let DesignationCounts = Group.GroupBy(Function(r) r("Designation").ToString()).ToDictionary(Function(g) g.Key, Function(g) g.Count())
 Let DesignationColumns = DesignationCounts.Keys.OrderBy(Function(key) key).ToList()
 Let DataArray = {Country}.Concat(DesignationColumns.Select(Function(col) DesignationCounts.TryGetValue(col, 0).ToString())).ToArray()
 Select YourDataTable.NewRow().ItemArray = DataArray
).CopyToDataTable()

Hope this helps

Cheers @indiedev91

[indiedev91]

@Palaniyappan

datatable =
(From row In YourDataTable.AsEnumerable()
 Group By Country = row("Country").ToString() Into Group
 Let DesignationCounts = Group.GroupBy(Function(r) r("Designation").ToString()).ToDictionary(Function(g) g.Key, Function(g) g.Count())
 Let DesignationColumns = DesignationCounts.Keys.OrderBy(Function(key) key).ToList()
 Let DataArray = {Country}.Concat(DesignationColumns.Select(Function(col) DesignationCounts.TryGetValue(col, 0).ToString())).ToArray()
 Select YourDataTable.NewRow().ItemArray = DataArray
).CopyToDataTable()

Where is the part where it matches the currentitem ?

[indiedev91]

While Using the code

(From row In in_InputFile.AsEnumerable()
 Group By Country = row("Country").ToString() Into Group
 Let DesignationCounts = Group.GroupBy(Function(r) r("Designation").ToString()).ToDictionary(Function(g) g.Key, Function(g) g.Count())
 Let DesignationColumns = DesignationCounts.Keys.OrderBy(Function(key) key).ToList()
 Let DataArray = {Country}.Concat(DesignationColumns.Select(Function(col) DesignationCounts.TryGetValue(col, 0).ToString())).ToArray()
 Select dt_Output.NewRow().ItemArray = DataArray
).CopyToDataTable()

Im facing this error
Error ERROR Validation Error BC31080: Operator '=' is not defined for types 'Object()' and 'String()'. Use 'Is' operator to compare two reference types. Framework/Process.xaml

[Palaniyappan (Forum Leader)]

Can u give a try with the

(From row In in_InputFile.AsEnumerable()
 Group By Country = row("Country").ToString() Into Group
 Let DesignationCounts = Group.GroupBy(Function(r) r("Designation").ToString()).ToDictionary(Function(g) g.Key, Function(g) g.Count())
 Let DesignationColumns = DesignationCounts.Keys.OrderBy(Function(key) key).ToList()
 Let DataArray = {Country}.Concat(DesignationColumns.Select(Function(col) DesignationCounts.TryGetValue(col, 0).ToString())).ToArray()
 Select dt_Output.Rows.Add(DataArray)
).CopyToDataTable()

Cheers @indiedev91

[indiedev91]

Its giving True inplace of the numbers and where there there is 0 its giving nullvalue

Country,ABC,POR,PQR,DEF,wxy,Avr,ABc,MNP,GHI,xyz,FHI,STV
Belarus,True,True,True,True,True,,,,,,,
Kazakhstan,True,True,True,True,,,,,,,,
Russia,True,True,True,True,True,True,True,True,True,True,,
Bangladesh,True,True,True,True,True,True,True,True,True,True,True,True
Cambodia,True,True,True,True,,,,,,,,
Cameroon,True,True,True,True,True,,,,,,,
Ecuador,True,True,True,,,,,,,,,
Egypt,True,True,True,True,True,True,True,,,,,
Ivory Coast,True,True,True,True,,,,,,,,
Kenya,True,True,True,True,,,,,,,,
Malaysia,True,True,True,True,True,,,,,,,
Mauritius,True,True,,,,,,,,,,
Mexico,True,True,True,True,True,True,True,True,,,,
Morocco,True,True,True,True,True,True,True,True,,,,
Myanmar,True,True,True,True,True,True,True,True,,,,