Merge multiple rows in one

Help Studio
, , , ,
1

I want to merge matching rows ( excluding one column) in to one. The only difference is lead passenger 1 column.

Input

image
image923×131 5.32 KB

I want Output like this

image
image1134×127 5.01 KB

Thanks in Advance

2

HI @ravindar.singh

How about this expression?

(From d In BuildDT.AsEnumerable
Group d By k=d("Booking No").toString.Trim Into grp = Group
Let nj = String.Join(" ",grp.Select(Function (n) n("Leading passenger 1").toString.Trim))
Let ra = New Object(){k,grp.First()(1),grp.First()(2),nj,grp.First()(4)}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

2 Likes
4

Hey @ravindar.singh,

Use the below code and try.

(From row In dt1.AsEnumerable
        Group row By key = New With { 
        Key.name = row.Item("Name")} Into grp = Group
        Select dt1Clone.LoadDataRow(New Object() {
                key.name,
                String.Join("","",grp.Select(Function(x)x(""Address"").ToString).ToArray)
        }, True)).CopyToDataTable

Thanks,
Sanjit

5

@Gokul001 .
Thank you for your response. It’s kinda of working but it’s showing the data twice

image
image1503×84 5.89 KB

6

Kindly share the Sample excel file @ravindar.singh

7

@Gokul001 Please see the file.
The real data will have multiple columns and multiple rows with different rows. The only columns that would be different is “Lead Passenger 1”
(10.3 KB)

8

HI @ravindar.singh

Check out the XAML file

DataScrappingInternetDown.xaml (10.1 KB)

Output

image

Regards
Gokul

1 Like
9

Hi @ravindar.singh

Check out the XAML file Based on the Input Excel file

DataScrappingInternetDown.xaml (13.1 KB)

ANR_Passenger_2022_09_06.xlsx (10.8 KB)

Output

image
image879×155 9.69 KB

Regards
Gokul

10

@Gokul001 thanks for your help - is there a way where I don’t have to create a output DT. As I might have over 30-40 columns.

Below is my current process

image
image764×620 55.7 KB

11

I don’t think so it is possible without creating an DtOutput @ravindar.singh

12

Okay Thanks for your help you are amazing.

13

@Gokul001 Actually I ran in to a problem.
Can I group by Booking No and Foreign Source BID?

image
image714×411 10.3 KB

14

Hi @ravindar.singh

How about this expression ?

(From d In DtBuild.AsEnumerable
Group d By k=d("Booking No").toString.Trim, k2=d("Foreign Source BID").ToString.Trim Into grp = Group
Let nj = String.Join(" ",grp.Select(Function (n) n("Leading passenger 1").toString.Trim))
Let ra = New Object(){k,grp.First()(1),grp.First()(2),nj,k2}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

2 Likes
15

It worked thanks again

16

Is this a javascript code?

17

No @gokul1904

18

Okay . Can you please let me know what type of code that is?

Thanks

19

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