topic Re: calculating total in script in QlikView

calculating total in script

berryandcherry6 — Wed, 22 Mar 2017 10:42:22 GMT

Hi,

i have below like table in script

Table:

Load * inline

[ clientID,BatchID

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

Re: calculating total in script

Anil_Babu_Samineni — Wed, 22 Mar 2017 11:00:53 GMT

You can use, Simple

clientID as Dimension

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

Re: calculating total in script

berryandcherry6 — Wed, 22 Mar 2017 11:02:38 GMT

Hi,

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

Re: calculating total in script

vinieme12 — Wed, 22 Mar 2017 11:18:34 GMT

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?

Re: calculating total in script

stabben23 — Wed, 22 Mar 2017 11:19:26 GMT

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

Re: calculating total in script

Anil_Babu_Samineni — Wed, 22 Mar 2017 12:46:41 GMT

Check stabben23 Reply

Re: calculating total in script

berryandcherry6 — Wed, 22 Mar 2017 12:52:36 GMT

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.

Re: calculating total in script

vinieme12 — Wed, 22 Mar 2017 12:58:08 GMT

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

Re: calculating total in script

berryandcherry6 — Wed, 22 Mar 2017 12:58:32 GMT

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.