calculating total in script

[berryandcherry6]

Hi,

i have below like table in script

Table:

Load * inline

[ clientID,BatchID

3,12

3,12

3,12

3,13

3,14

3,15

3,13

3,15

];

Table2:

Load * inline

[BatchID,CampID,sales,advertise

12,2,23,21

12,3,4,5

12,4,3,6

13,5,46,87

14,6,12,23

15,7,34,12

13,8,56,2

15,9,23,5

];

Load  (sum(sales)/sum(advertise)) as Percetage,

CampID

Resident Table2  Group By CampID;

This gives me Percentage for each CampID, but i need to get  percentage ( i.e sum(sales)/sum(advertise))  for clientID.

My result should be

clientID  Percetage

3             1.248

Please help me on this

Regards,

Supriya

[Anil_Babu_Samineni]

You can use, Simple

clientID as Dimension

and expression should be sum(sales)/sum(advertise) // Because, clientID is already associate with you

[berryandcherry6]

Hi,

But i need to this in script itself. Can i do this in script?

[vinieme12]

your data sample doesn't seem right

you have the same client Id repeated for the same batch, which has 3 different rows in the second table this way you will have 3 x 3 rows for the same client id,

is that correct?

[stabben23]

Hi,

Change Your script:

Table:

Load * inline

[ clientID,BatchID
  3,12
3,12
  3,12
  3,13
3,14
3,15
3,13
3,15
  ];

Table2:
  Left Join Load * inline //Left Join here
  [BatchID,CampID,sales,advertise
  12,2,23,21
  12,3,4,5
12,4,3,6
  13,5,46,87
  14,6,12,23
  15,7,34,12
  13,8,56,2
15,9,23,5
];
 
New:
Load  (sum(sales)/sum(advertise)) as Percetage,
  clientID
Resident Table  Group By  clientID; //Group by clientID
  Drop Table Table; //Drop table

[Anil_Babu_Samineni]

Check stabben23 Reply

[berryandcherry6]

Hi Vineeth,

Sorry for delay reply.

Yes, you are right we will have 3 x 3 rows for the same client id, beacuse each client has different batches.

You could see below attached qvf file for better understanding. Let me know if you need more information.

[vinieme12]

then first left join to create a single table and then aggregate like Staffan has shown above

[berryandcherry6]

yes, Anil. Its right according to this simple example,and giving expected output.

But my data model is little complex, here i am not getting how could i incorporate  stabben23 logic.

Can you or Staffan help me to solve this issue i have attached qvf file. Please help me on this.