Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Creator
Creator

Conditional counter on resident table

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

1 Solution

Accepted Solutions
AshutoshBhumkar
Partner
Partner

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:

AshutoshBhumkar_0-1635156794459.png

 

Thanks,

Ashutosh

View solution in original post

2 Replies
AshutoshBhumkar
Partner
Partner

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:

AshutoshBhumkar_0-1635156794459.png

 

Thanks,

Ashutosh

View solution in original post

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
];