Conditional counter on resident table

[chrismtb]

Hi all,

I have something that is a bit of a head scratcher. I have been close to an answer a couple of times but not getting the desired result.

I have a data set loaded into my model - for this question I have simplified my data. I am taking a resident load using "order by" to put my data into the order I want it. my source looks as follows:

ID	Project Name	Area	Status
100	Project A	A	backlog
101	Project B	A	backlog
106	Project C	A	backlog
110	Project D	A	inflight
111	Project E	B	complete
112	Project F	C	backlog
114	Project G	C	complete

what I want is a counter that works with the Area and status column i.e. if the AREA is the same as the previous area and the STATUS is the same as the previous status I want the counter to increment by 1... if the status is a new status or the area is a new area then the  counter resets to 1.

so my output would be: 

ID	Project Name	Area	Status	Counter
100	Project A	A	backlog	1
101	Project B	A	backlog	2
106	Project C	A	backlog	3
110	Project D	A	inflight	1
111	Project E	B	complete	1
112	Project F	C	backlog	1
114	Project G	C	complete	1

I would be happy to combine area and status into a concatenated column if that would help with the calculation.

Any help would be appreciated.

Thanks

Chris

[AshutoshBhumkar · Accepted solution]

Hello,

I have tried with below sample data got the out put attached below. Please relate and check for your actual data.

Data:
Load * Inline [
ID, Project Name,Area,Status
100,Project A, A, backlog
101,Project B, A, backlog
106,Project C, A, backlog
110,Project D, A, inflight
111,Project E, B, complete
112,Project F, C, backlog
114,Project G, C, complete
];

OrderedData:
Load * ,
'Temp' as Temp
Resident Data Order by Area,Status Asc;
Drop Table Data;

Counter:
Load *,

If(Area = Previous(Area) and Status = Previous(Status),RangeSum(1,Peek(Counter)),1) as Counter

Resident OrderedData;
Drop Table OrderedData;

Output:

Thanks,

Ashutosh

[Saravanan_Desingh]

One more solution,

tab1:
Load *, AutoNumber(RowNo(),Area&Status) As Counter Inline [
ID, Project Name,Area,Status
100,Project A, A, backlog
101,Project B, A, backlog
106,Project C, A, backlog
110,Project D, A, inflight
111,Project E, B, complete
112,Project F, C, backlog
114,Project G, C, complete
];