Merge rows and join columns

[umashankarus]

Hi Experts,

Can we merge rows in a table and join the columns (fields)

I have attached example of input table and expected resultant table

Thanks for your inputs

- Umashankar

[aarkay29]

Try this

Table:

LOAD * INLINE [

ID,F1,F2,F3,F4,F5

12341,1,2

12342,23,24

12343,22,55

12344,44,432

12341,,,,11

12343,,,,66,77

];

NoConcatenate

LOAD

     ID ,

     Sum(F1) AS F1,

     Sum(F2) AS F2,

     Sum(F3) AS F3,

     Sum(F4) AS F4,

     Sum(F5) AS F5

Resident

       Table

GROUP BY

       ID;

DROP Table

     Table;

[surendraj]

Did you try with  like AGGR(Distnict(ID)).....?

[umashankarus]

Hi,

Thanks for the response

One of the column (Field3) holds only string values and not numbers

In such case, it displays null value in the resultant table. Any other function that we need to use instead of Sum(Field3) ?

[vishsaggi]

Is this you are looking for?

Load your table as is and use Straight table with Expressions as your fields like.

Straight table.

Dim: ID

Expr: Sum(F1)

         Sum(F2).....

         Sum(F5)

[buzzy996]

just use group id on ur load script,that helps to merge the rows based on id.

[umashankarus]

Hi,

Thanks for the response

I used 5 expression one for each field

AGGR(Field1,ID)

AGGR(Field2,ID)

AGGR(Field3,ID)

AGGR(Field4,ID)

AGGR(Field5,ID)

This seems to work and I get the resultant table with merged / grouped rows (based on ID) and the columns joined

But my real data has 100s of columns, which would mean the number of expressions to be repeated

Is there options to simplify this in the expression

[aarkay29]

If field consists of string values then i would recommend to drop the the field the from the table. Use group by only for the fields containing numerical and then apply match the string field to the above table.

[aarkay29]

Try this

let say F4 has string values

Table:

LOAD * INLINE [

ID,F1,F2,F3,F4,F5

12341,1,2

12342,23,24

12343,22,55

12344,44,432

12341,,,,ab

12343,,,,bcd,77

];

NoConcatenate

LOAD

     ID ,

     Sum(F1) AS F1,

     Sum(F2) AS F2,

     Sum(F3) AS F3,

     CONCAT(F4) AS F4,

     Sum(F5) AS F5

Resident

       Table

GROUP BY

       ID;

DROP Table

     Table;

[MarcoWedel]

your table might be a concatenation of two tables,

one being "ID, Field1, Field2" and the other "ID, Field3, Field4, Field5".

If so then try with joining those tables on the ID field instead of concatenating.

hope this helps

regards

Marco