Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have table like this
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
I want to load this table by merging similar rows in to one like this
9
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
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, 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
];
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;
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;
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, 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
];
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;
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
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, 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.
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
What is your expected output?
Like this ?
G, Amount
1,1000
2,800
3,800
1,200
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
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