Output According to given condition

[lionking15]

Hi,

Kindly find below given dummy data

Base Table-  

Accnt Number	Agent	Amount	Capping	Date
1	a	10	30	3/1/2017
1	a	10	30	4/6/2017
1	b	18	30	8/6/2017
1	c	30	30	8/26/2017
2	a	40	30	4/1/2017
2	b	70	30	4/25/2017
3	b	50	30	5/1/2017
4	c	60	30	5/26/2017
5	a	20	30	3/2/2017
5	c	20	30	4/22/2017
5	a	10	30	5/23/2017
6	c	80	30	6/22/2017

Output Required-

Accnt Number	Agent	Sum(Amount)	Capping	Required Field
1	a	20	30	1
1	b	18	30	2
1	c	30	30	3
2	a	40	30	1
2	b	70	30	2
3	b	50	30	1
4	c	60	30	1
5	a	30	30	1
5	c	20	30	2
6	c	80	30	1

My Requirement

1. i should get sum of amount as per Accnt Number and Agent
2. i should give output(i.e Required Field)as per first occurrence of Accnt Number and Agent on Date.

Note-The output to be obtained from front end and not from the back end.

[lionking15]

Hi,

And One 'Accnt Number' can have multiple 'Agent' and that number is not fixed.

[vinieme12]

use Autonumber()

LOAD AccntNumber

          Agent,

          Capping,

          Sum(Amount) as Sum_Amt,

           Autonumber(Agent & Date , Agent) as NEWFIELD

FROM XX

Group by AccntNumber,Agent,Capping;

[hari8088]

Hi,

See the attached qvw.

[effinty2112]

Hi Giridhar,

Maybe:

Accnt Number	Agent	Sum(Amount)	min(Capping)	RowNo()
1	a	20	30	1
1	b	18	30	2
1	c	30	30	3
2	a	40	30	1
2	b	70	30	2
3	b	50	30	1
4	c	60	30	1
5	a	30	30	1
5	c	20	30	2
6	c	80	30	1

Regards

Andrew

[vinayakg]

Hi Giridhar,

Please Find the attached File, It may Help u to get ur required answer.