Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
pthai
Contributor III
Contributor III

Looking for a solution

Hello guys,

I have a problem to calculate the point used of a recipent. I need to know the point which recipent consumed.

pthai_0-1649235939513.png

Here is an example for recipent B

pthai_0-1649299781657.png

 

 

Here is my expected result

Recipent Sum of Used Point
A 0
B 5
F 0
K 0

 

Could anyone help me find the answer here? Thank you.

Labels (5)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

here is the updated script and expression

 

Raw:
Load *,Sender&'-'& Recipient as keyfield Inline [
Recipient,Sender,date,point
A,B,2022-04-01,3
A,C,2022-04-13,2
B,E,2022-04-10,1
B,D,2022-04-12,2
F,H,2022-04-22,2
K,B,2022-04-21,2
];

SenderReceivers:
Load
Sender as Users,'Sender' as type, keyfield
Resident Raw;
Concatenate(SenderReceivers)
Load
Recipient as Users,'Recipient' as type, keyfield
Resident Raw;

 

InChart

Dimension = =aggr(only({<type={'Recipient'}>}Users),Users)

Expression = if(sum({<type={'Sender'}>}point) and sum({<type={'Recipient'}>}point) , sum({<type={'Sender'}>}point) ,sum(0))

 

qlikCommunity.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

7 Replies
vinieme12
Champion III
Champion III

You will need to create another table linked to your raw data 

 

Raw:

Load *

, Sender&'-'& Recipient as keyfield 

From xxxxx;

 

SenderReceivers:

Load

Sender as Users, keyfield 

Resident Raw;

Concatenate(SenderReceivers)

Load

Recipient as Users, keyfield 

Resident Raw;

 

Then use the Users field from the new table in your charts

 

Dimension: only({<Users=p(Recipient)>}Users)

Measure: sum({<User=p(Sender)>*<User=p(Recipient)>}point)

 

 

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pthai
Contributor III
Contributor III
Author

Hi Vini,

Thank you so much for your response. But, it not work. Here is my updated file with your idea.

I show an example for the recipent B as below.

pthai_0-1649299509510.png

 

vinieme12
Champion III
Champion III

Dimension:

aggr(only({<Users=p(Recipient)>}Users),Users)

Measure: sum({<User=p(Sender)>}point)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pthai
Contributor III
Contributor III
Author

Not work 😞

pthai_0-1649312757940.png

 

pthai
Contributor III
Contributor III
Author

Hello

Anyone can help me??? :'( 

vinieme12
Champion III
Champion III

here is the updated script and expression

 

Raw:
Load *,Sender&'-'& Recipient as keyfield Inline [
Recipient,Sender,date,point
A,B,2022-04-01,3
A,C,2022-04-13,2
B,E,2022-04-10,1
B,D,2022-04-12,2
F,H,2022-04-22,2
K,B,2022-04-21,2
];

SenderReceivers:
Load
Sender as Users,'Sender' as type, keyfield
Resident Raw;
Concatenate(SenderReceivers)
Load
Recipient as Users,'Recipient' as type, keyfield
Resident Raw;

 

InChart

Dimension = =aggr(only({<type={'Recipient'}>}Users),Users)

Expression = if(sum({<type={'Sender'}>}point) and sum({<type={'Recipient'}>}point) , sum({<type={'Sender'}>}point) ,sum(0))

 

qlikCommunity.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pthai
Contributor III
Contributor III
Author

Thank you so muchh, vinieme12

It work now.