Linq for joining rows

Help Studio
, ,
1

Hi Team,

I am using this query now,

(From d In InputFile.AsEnumerable()
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim() Into grp = Group
Let s12 = String.Join(“,”, grp.Select(Function(x) x(12).ToString()))
Let s13 = String.Join(“,”, grp.Select(Function(x) x(13).ToString()))
Let s = s12 + “,” + s13
Let ra = New Object() {k1, k2, s}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

It is working but it is joining blank rows as well. How to tackle it.

Presently the output is like:

image
image1170×144 20.1 KB

See the last 2 columns, but it dont want blank rows to be joined. Please help

2

@yashchoursia25

try this


(From d In InputFile.AsEnumerable()
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim() Into grp = Group
Let s12 = If(String.Join(",", grp.Select(Function(x) x(12).ToString())).Replace(",","").Trim.Equals(String.Empty),"",String.Join(",", grp.Select(Function(x) x(12).ToString())))
Let s13 = If(String.Join(",", grp.Select(Function(x) x(13).ToString())).Replace(",","").Trim.Equals(String.Empty),"",String.Join(",", grp.Select(Function(x) x(13).ToString())))
Let s = s12 + "," + s13
Let ra = New Object() {k1, k2, s}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

cheers

3 
(From d In InputFile.AsEnumerable()
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim() Into grp = Group
Let e12 = grp.Select(Function(x) x(12).ToString().Trim)
Let e13 = grp.Select(Function(x) x(13).ToString().Trim)
Let ec = e12.Concat(e13).Where(Function (x) Not String.IsNullOrEmpty(x))
Let s = String.Join("," , ec)
Let ra = New Object() {k1, k2, s}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()
4

Sorry @ppr and @Anil_G , i posted the wrong query. Attaching y query below:

(From d In InputFile.AsEnumerable()
Where d(12).ToString isNot Nothing and d(13).ToString isNot Nothing
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim()
Into grp = Group
Let s13 = String.Join(“,”, grp.Select(Function(x) x(13).ToString()))
Let s12 = String.Join(“,”, grp.Select(Function(x) x(12).ToString()))
Let ra = New Object() {k1, k2, s12, s13}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

5

@yashchoursia25

Please check this

(From d In InputFile.AsEnumerable()
Where d(12).ToString isNot Nothing and d(13).ToString isNot Nothing
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim()
Into grp = Group
Let s12 = If(String.Join(",", grp.Select(Function(x) x(12).ToString())).Replace(",","").Trim.Equals(String.Empty),"",String.Join(",", grp.Select(Function(x) x(12).ToString())))
Let s13 = If(String.Join(",", grp.Select(Function(x) x(13).ToString())).Replace(",","").Trim.Equals(String.Empty),"",String.Join(",", grp.Select(Function(x) x(13).ToString())))
Let ra = New Object() {k1, k2, S12,S13}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

cheers

6

Again joining the blank ones as well, see

image
image1267×232 26.3 KB

see the last column with ,

7

@ppr any inputs from your side?

8

Along with some other doubts

When d(12) is null, a toString will cause an exception

When it is about checking that d(12), d(13) is not null then we can do:

(From d In InputFile.AsEnumerable()
Where Not isNothing(d(12))
Where Not isNothing(d(13))
…

but still,the rest of the LINQ is not really matching the other parts of your question and still creates some doubts on the aimed goal

9

@yashchoursia25

Try this

(From d In InputFile.AsEnumerable()
Where d(12).ToString isNot Nothing and d(13).ToString isNot Nothing
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim()
Into grp = Group
Let s12 = String.Join(",", grp.Where(function(x) Not String.IsNullOrEmpty(x(12).ToString().Trim)).Select(Function(x) x(12).ToString()))
Let s13 = String.Join(",", grp.Where(function(x) Not String.IsNullOrEmpty(x(13).ToString().Trim)).Select(Function(x) x(13).ToString()))
Let ra = New Object() {k1, k2, S12,S13}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

cheers

1 Like
10

Hi @yashchoursia25
Try this

(From d In InputFile.AsEnumerable()
Group d By k1 = d(6).ToString().Trim(), k2 = d(8).ToString().Trim() Into grp = Group
Let s12 = String.Join(“,”, grp.Where(Function(x) Not String.IsNullOrWhiteSpace(x(12).ToString())).Select(Function(x) x(12).ToString()))
Let s13 = String.Join(“,”, grp.Where(Function(x) Not String.IsNullOrWhiteSpace(x(13).ToString())).Select(Function(x) x(13).ToString()))
Let s = s12 + “,” + s13
Let ra = New Object() {k1, k2, s}
Select joiningDT.Rows.Add(ra)).CopyToDataTable()

Hope it helps!!

11

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