topic Re: How to merge matching rows in QlikView

How to merge matching rows

Wed, 10 Sep 2014 03:46:37 GMT

Hi I have table like this

A B C D E F Amount

1 2 3 4 5 6 500

1 2 7 4 5 6 400

1 2 8 4 5 6 200

I want to load this table by merging similar rows in to one like this

A B C D E F sum(Amount)

1 2 3 4 5 6 1000

1 2 7 4 5 6 800

1 2 8 4 5 6 200

Thanks

Pavan

Re: How to merge matching rows

nagaiank — Wed, 10 Sep 2014 03:54:29 GMT

Try the following:

LOAD * Inline [
A,B,C,D,E,F,Amount
1,2,3,4,5,6, 500
1,2,3,4,5,6, 500
1,2,7,4,5,6, 400
1,2,7,4,5,6, 400
1,2,8,4,5,6, 200
];
Table2:
NoConcatenate LOAD A,B,C,D,E,F,Sum(Amount) as Value
Resident Table1 Group By A,B,C,D,E,F;

Re: How to merge matching rows

ecolomer — Wed, 10 Sep 2014 04:12:31 GMT

In the script use Resident Group:

ZZ:

LOAD * Inline [

A, B, C, D, E, F, Amount

1, 2, 3, 4, 5, 6, 500

1, 2, 7, 4, 5, 6, 400

1, 2, 8, 4, 5, 6, 200

];

XX:

LOAD

A, B, C, D, E, F,

sum(Amount) as TAmount

resident ZZ

group by A,B,C,D,E,F;

DROP TABLE ZZ;

Re: How to merge matching rows

PrashantSangle — Wed, 10 Sep 2014 04:26:50 GMT

Hi,

If you want to do in script level then above solution must work for you.

If you want in front end

Simply take Straight table

Add Dimension A,B,C,D,E,F

and Write Expression Sum(Amount)

It will give you desired result.

for your reference i am attaching sample file also.

Regards

Re: How to merge matching rows

Wed, 10 Sep 2014 04:49:33 GMT

Thanks Friends

I have new problem now. I added new column G for the above table.

ZZ:

LOAD * Inline [

A, B, C, D, E, F, G, Amount

1, 2, 3, 4, 5, 6, 1, 500

1, 2, 7, 4, 5, 6, 2, 400

1, 2, 7, 4, 5, 6, 3, 400

1, 2, 8, 4, 5, 6, 1, 200

];

I want to group by A B C D E F But I want to see G rather all the remaining values when the user mouse over the Amount cell.

Re: How to merge matching rows

aveeeeeee7en — Wed, 10 Sep 2014 04:51:22 GMT

Try this:

Table1:
Load * Inline [
A, B, C, D, E, F, Amount
1, 2, 3, 4, 5, 6, 500
1, 2, 3, 4, 5, 6, 500
1, 2, 7, 4, 5, 6, 400
1, 2, 7, 4, 5, 6, 400
1, 2, 8, 4, 5, 6, 200
];

Concatenate(Table1)

Load * Inline [
A, B, C, D, E, F, Amount
1, 2, 3, 4, 5, 6, 1000
1, 2, 7, 4, 5, 6, 800
1, 2, 8, 4, 5, 6, 200
];

After this, Reload the Application.

Use A,B,C,D,E,F as Dimensions

& Use Sum(Amount) as Expression.

See the Attachment.

Regards

Aviral Nag

Re: How to merge matching rows

anbu1984 — Wed, 10 Sep 2014 05:24:11 GMT

What is your expected output?

Like this ?

G, Amount

1,1000

2,800

3,800

1,200

Re: How to merge matching rows

Wed, 10 Sep 2014 05:36:17 GMT

no like this when the user mouse over 1000

it should pop up as

A, B, C, D, E, F, G, Amount

1, 2, 3, 4, 5, 6, 1, 500

If G column different also it should display like this

A, B, C, D, E, F, G, Amount

1, 2, 3, 4, 5, 6, 1, 500

1, 2, 3, 4, 5, 6, 2 , 500