Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

malpranati
New Contributor

QlikSense Resident load

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.

3 Replies

Re: QlikSense Resident load

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;

Partner
Partner

Re: QlikSense Resident load

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;

Highlighted
MVP & Luminary
MVP & Luminary

Re: QlikSense Resident load

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
];


talk is cheap, supply exceeds demand