Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have following Table :
Mono | Color | Sipmlex | Duplex |
1 | 2 | 3 | 4 |
5 | 6 | 7 | 8 |
9 | 10 | 11 | 12 |
Now I want to merge All 4 column into one column name ' Type ' and add one additional column name ' Value ' where I show the sum like following:
Type Value
------- ------------
Mono 15
Color 18
Simplex 21
Duplex 24
How I got this??
Hi,
so you can get this table:
Basis:
LOAD * INLINE [
Mono, Color, Sipmlex, Duplex
1, 2, 3, 4
5, 6, 7, 8
9, 10, 11, 12
];
Table:
Load
'Mono' as Type,
SUM(Mono) as Value
RESIDENT Basis
GROUP BY 'Mono';
LOAD
'Color' as Type,
SUM(Color) as Value
RESIDENT Basis
GROUP BY 'Color';
LOAD
'Sipmlex' as Type,
SUM(Sipmlex) as Value
RESIDENT Basis
GROUP BY 'Sipmlex';
LOAD
'Duplex' as Type,
SUM(Duplex) as Value
RESIDENT Basis
GROUP BY 'Duplex';
you can try something like this..
Table:
Load
'Mono' as Type,
Mono as Value
From $(FileName);
Concatenate (Table)
LOAD
'Color' as Type,
Color as Value
From $(FileName);
Concatenate (Table)
LOAD
'Sipmlex' as Type,
Sipmlex as Value
From $(FileName);
Concatenate (Table)
LOAD
'Duplex' as Type,
Duplex as Value
From $(FileName);
hope that helps..
Hi,
so you can get this table:
Basis:
LOAD * INLINE [
Mono, Color, Sipmlex, Duplex
1, 2, 3, 4
5, 6, 7, 8
9, 10, 11, 12
];
Table:
Load
'Mono' as Type,
SUM(Mono) as Value
RESIDENT Basis
GROUP BY 'Mono';
LOAD
'Color' as Type,
SUM(Color) as Value
RESIDENT Basis
GROUP BY 'Color';
LOAD
'Sipmlex' as Type,
SUM(Sipmlex) as Value
RESIDENT Basis
GROUP BY 'Sipmlex';
LOAD
'Duplex' as Type,
SUM(Duplex) as Value
RESIDENT Basis
GROUP BY 'Duplex';
Hi Jai,
The above is one way to do it, however if you have many values in 'Type' or if they vary, you code may need a lot of manual intervention.
I would rather use Crosstable to solve this. Please find an example file attached. Hope it works for you
Regards
RL
Thanx a lot lakhina.
Thank you very much Aadil.
Thanx Stefan its working.