Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
beck_bakytbek
Master
Master

Flag for aggregated values

Hi Folks,

i got  a question, my data do look like:

 

Customer, Amount, ExpectedValue = Flag

Adam, 100,                      1

Adam, 50,                        1

Smith, 80,                       2 

Smith, 10,                       2

Oliver, 90,                       1

Oliver, 30,                       1

 

my issue is, how can i create the flag on the base of aggregated values, for example

Amount of Adam is greater than 100  = 1

Amount of Smith is lower than 100 = 2

Amount of Oliver is greater than 100 = 1

 

i am using the function sum () and group by () and the Field: Flag gives for each line the value, i want to give the aggregated value for each customer.

 

does anybody have any idea, how to rectify this issue?

Thanks a lot 

Beck

 

 

Labels (1)
1 Solution

Accepted Solutions
Ponkaviyarasu
Partner
Partner

Hi Beck,

Thanks for your reply.

My previous comment will satisfy your scenario. If incase, you want to achieve by using Sum() & Groupby() function, below code will help you.

Test:
Load * Inline [
Customer, Invoice, Amount

Adam, 1212S, 100

Adam, 1214S, 50

Smith, 1516S, 80

Smith, 1515S, 10

Oliver, 2020s, 90

Oliver, 2021S, 30
];

 

Temp_T2:
Load
Customer,
Sum(Amount) as Tot_Amount
Resident Test
group by Customer;

NoConcatenate


T2:
Load
Customer,
if(Tot_Amount>100,'1','2') as Flag
Resident Temp_T2;

Drop Table Temp_T2;

Join

Load * Resident Test;

Drop table Test;

 

Output:

Ponkaviyarasu_0-1623934998768.png

 

**Confirm me, you're looking above result.

Thanks.

View solution in original post

4 Replies
Ponkaviyarasu
Partner
Partner

Hi Beck,

Hope below expression works for you, 

Load

Customer,

Amount,

if(Amount>100,'1','2') as Flag

from Table;

 

Thanks,

Kavi

beck_bakytbek
Master
Master
Author

Hi Ponkaviyarasu,

thanks a lot for your help and valuable time, i think i overlooked one point in this issue, my data do look like:

Customer, Invoice, Amount, ExpectedValue = Flag

Adam, 1212S, 110,                      1

Adam, 1214S, 50,                        1

Smith, 1516S, 80,                       2 

Smith, 1515S, 10,                       2

Oliver, 2020s, 150,                       1

Oliver, 2021S, 30,                       1

 

i am using the function sum () and group by () and the Field: Flag gives for each line the value, i want to give the aggregated value for each customer like Adam = 1 and Smith 2.

I hope i could explain well enough, let me know if i overlooked something

Thanks a lot

Ponkaviyarasu
Partner
Partner

Hi Beck,

Thanks for your reply.

My previous comment will satisfy your scenario. If incase, you want to achieve by using Sum() & Groupby() function, below code will help you.

Test:
Load * Inline [
Customer, Invoice, Amount

Adam, 1212S, 100

Adam, 1214S, 50

Smith, 1516S, 80

Smith, 1515S, 10

Oliver, 2020s, 90

Oliver, 2021S, 30
];

 

Temp_T2:
Load
Customer,
Sum(Amount) as Tot_Amount
Resident Test
group by Customer;

NoConcatenate


T2:
Load
Customer,
if(Tot_Amount>100,'1','2') as Flag
Resident Temp_T2;

Drop Table Temp_T2;

Join

Load * Resident Test;

Drop table Test;

 

Output:

Ponkaviyarasu_0-1623934998768.png

 

**Confirm me, you're looking above result.

Thanks.

View solution in original post

beck_bakytbek
Master
Master
Author

Hi Pokaviyarasu, thanks a lot for your help and your valuable time