Want to count the custIDs in last three months

[Srinivas]

Hi All,

I have data like custdids , date, sales and Prdids so on.., this one custids may be repeated or may not in last three months if repeated we need to count how many times it is repeating.

based on above senario we need to display one custid how many times repeated in last three months.

Data is like below:

CustID	Date	Sales	ProdID
1235	01-01-2018	100	5321
1236	01-02-2018	200	5322
1237	02-03-2018	300	5323
1238	03-04-2018	400	5324
1239	04-05-2018	500	5325
1235	01-01-2018	600	6321
1236	01-02-2018	700	6322
1239	02-03-2018	800	6323
1245	03-04-2018	900	5321
1246	04-05-2018	1000	5322
1235	01-01-2018	1100	5323
1248	01-02-2018	1200	5324
1239	04-05-2018	1300	5325

Output like below:

ProdID	Sales	Date	CustID	Count
5321	100	01-01-2018	1235	1
5322	200	01-02-2018	1236	1
5323	300	02-03-2018	1237	1
5324	400	03-04-2018	1238	1
5325	500	04-05-2018	1239	1
6321	600	01-01-2018	1235	2
6322	700	01-02-2018	1236	2
6323	800	02-03-2018	1239	2
5321	900	03-04-2018	1245	1
5322	1000	04-05-2018	1246	1
5323	1100	01-01-2018	1235	3
5324	1200	01-02-2018	1248	1
5325	1300	04-05-2018	1239	2

Thanks,

Srinivas

[sibin_jacob · Accepted solution]

Please use the below script

Test:

load *,1 as Count;

Load * inline [

CustID,Date,Sales,ProdID

1235,01-01-2018,100,5321

1236,1-02-2018,200,53221

1237,02-03-2018,300,5323

1238,03-04-2018,400,5324

1239,04-05-2018,500,5325

1235,01-01-2018,600,6321

1236,01-02-2018,700,6322

1239,02-03-2018,800,6323

1245,03-04-2018,900,5321

1246,04-05-2018,1000,5322

1235,01-01-2018,1100,5323

1248,01-02-2018,1200,5324

1239,04-05-2018,1300,5325

];

Test1:

NoConcatenate

Load CustID,Date,Sales,ProdID,if(CustID=Previous(CustID),Peek('Count')+1,Count) as Count Resident Test

Order by CustID;

DROP table Test;

[Srinivas]

Thanks Jacob,

It is more help full, Thank you once again.

Thanks,

Srinivas