QlikSense Resident load

[malpranati]

Hi All,

I have a table as follows:

Test:

ID, Amount

A,10

A,30

A,30

A,30

A,30

B,12

B,40

B,40

B,40

B,60

B,60

B,60

I want sum of those amounts where value of the amount is more than 20 and that value has frequency more than 2.

For example, in the above table for ID= A, 30 is greater than 20 and has frequency 4; for ID=B: 40 has frequency 3 and is >20. Also, 60 has frequency 3 and is >20. So the result should be as below

Result:

ID, Amount

A,120                  (//i.e. 30*4)

B,300                   (//i.e. (40*3 ) + (60*3))

I want this to be done in the load script. Any suggestions are welcome.

Thanks.

[settu_periasamy]

Hi,

Can you try this?

T1:
LOAD ID, Amount where Amount>20;
LOAD * Inline [
ID, Amount
A,10
A,30
A,30
A,30
A,30
B,12
B,40
B,40
B,40
B,60
B,60
B,60
];

NoConcatenate
T2:
LOAD ID, Sum(Amount) as Amount Resident T1 Group by ID;

DROP Table T1;

[hanna_choi]

Test:
Load *
inline [
ID, Amount
A,10
A,30
A,30
A,30
A,30
B,22
B,40
B,40
B,40
B,60
B,60
B,60
];

NoConcatenate
Result:
Load
ID,
sum(TotalAmount) as Amount
where CNTAmount > 1 and Amount > 20
group by ID
;
Load
ID,
Amount,
sum(Amount) as TotalAmount,
count(Amount) as CNTAmount
resident Test
group by ID, Amount
;

drop table Test;

[Gysbert_Wassenaar]

Try this:

T1:
LOAD ID, sum(Sum) as Amount
WHERE Count >2
GROUP BY ID;
LOAD ID, Amount, count(Amount) as Count, Amount*count(Amount) as Sum
WHERE Amount > 20
GROUP BY ID, Amount;
LOAD * Inline [
ID, Amount
A,10
A,30
A,30
A,30
A,30
B,12
B,40
B,40
B,40
B,60
B,60
B,60
];