Splitting data of a field based on count of another field

[#1]

Hi All,

I have data as below

table:

load * inline [

Donator_ID,Donator_Time,Donation1,Donation2,Donation3,Donation4,Donation5,Donation6,Acceptor_ID,Acceptor_Time,Acceptor_1,Acceptor_2,Acceptor_3,Acceptor_4,Acceptor_5,Acceptor_6

1,      12,    13,       62,      15,    88,     6,       5,         6,    12,          211,     - ,          38,       -,    144,        -  

1,      12,    13,       62,      15,    88,     6,       5,   7,    12,           -,       83,         - ,          -,    31,         -  

];

If my Donator_Time  is equal to  Acceptor_Time  then

step 1

i need to calculate the no of  Acceptor_ID

in the above data for my Donator_Time  for donator_ID 1 is 12

so for Donator_Time 12 i have two Acceptor_IDs  6 and 7

i can have any no of records

step2:

now i need is i want to  the calculation  Acceptor_1-(Donation1/2)   (Acceptor_1 minus(Donation1 divided by 2)

this will give me the below out put  for  acceptor

Acceptor_ID,	Acceptor_Time,	Acceptor_1_New	Acceptor_2_New	Acceptor_3_New	Acceptor_4_New	Acceptor_5_New	Acceptor_6_New
6	12	205	-	30	-	141	-
7	12	-	52	-	-	28	-

[vishsaggi]

Try this?

table:

load *, IF(Acceptor_Time = Donator_Time, 1,0) AS Flag inline [

Donator_ID,Donator_Time,Donation1,Donation2,Donation3,Donation4,Donation5,Donation6,Acceptor_ID,Acceptor_Time,Acceptor_1,Acceptor_2,Acceptor_3,Acceptor_4,Acceptor_5,Acceptor_6

1,      12,    13,       62,      15,    88,     6,       5,         6,    12,          211,     - ,          38,       -,    144,        - 

1,      12,    13,       62,      15,    88,     6,       5,   7,    12,           -,       83,         - ,          -,    31,         - 

];

Using straight table:

Dim

1. = IF(Flag = 1, Acceptor_ID)

2. = IF(Flag = 1, Acceptor_Time)

Expr:

= Acceptor_1 - (Donation1/2) ....... create 6 expressions till

.....

= Acceptor_6 - (Donation6/2)

[marcus_sommer]

Maybe a transforming of the data-structure per The Crosstable Load would be useful.

- Marcus