topic Re: Merge rows and join columns in QlikView

Merge rows and join columns

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Merge rows and join columns

aarkay29 — Wed, 15 Feb 2017 15:42:45 GMT

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;

Re: Merge rows and join columns

surendraj — Wed, 15 Feb 2017 15:47:43 GMT

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

Re: Merge rows and join columns

umashankarus — Wed, 15 Feb 2017 16:27:12 GMT

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) ?

Re: Merge rows and join columns

vishsaggi — Wed, 15 Feb 2017 16:37:16 GMT

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)

Re: Merge rows and join columns

buzzy996 — Wed, 15 Feb 2017 16:41:20 GMT

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

Re: Merge rows and join columns

umashankarus — Wed, 15 Feb 2017 16:53:34 GMT

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

Re: Merge rows and join columns

aarkay29 — Wed, 15 Feb 2017 17:05:25 GMT

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.

Re: Merge rows and join columns

aarkay29 — Wed, 15 Feb 2017 18:35:04 GMT

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;

Re: Merge rows and join columns

MarcoWedel — Sat, 18 Feb 2017 00:47:41 GMT

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