Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 - Specialist
Partner - Specialist

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 - Specialist
Partner - Specialist

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

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