Flag for aggregated values

[beck_bakytbek]

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

[Ponkaviyarasu]

Hi Beck,

Hope below expression works for you, 

Load

Customer,

Amount,

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

from Table;

Thanks,

Kavi

[beck_bakytbek]

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 · Accepted solution]

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:

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

Thanks.

[beck_bakytbek]

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