Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

fetch latest record with multiple status

Hi Experts,

I have a data where one CRMID got multiple USERS and the requirement is to show the user with active status when it got both active and block.

1.When user got one CRM ID show it the one status active/block

2.When CRMID got two USERS show then check for status if it has different status active and block then show only crmid with active status

3.when CRMID got two USERID and having same status active then pick the latest active status.

Below is the sample data set

 

User CRMID Status last updated
1 111 Active 2-Jan
2 111 Active 4-Feb
3 123 Active 2-Feb
4 125 Blocked 2-Feb
5 122 Active 4-Feb
6 122 Blocked 2-Feb

 

Expected output is 

User CRMID Status last updated
2 111 Active 4-Feb
3 123 Active 2-Feb
4 125 Blocked 2-Feb
5 122 Active 4-Feb
Labels (3)
8 Replies
Richadam
Contributor
Contributor

Thank you so much for sharing this method which looks really useful.

Rich Adam gb whatsapp
vinieme12
Champion III
Champion III

As below

Main:
Load 
User,CRMID,Status,[lastUpdated]
From YourSource;



Inner Keep(Main)
Load CRMID,max(lastUpdated) as lastUpdated
Resident Main
Group by CRMID;
Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
kvr9
Creator
Creator
Author

Hi ,

 

Thank you, the solution works fine for given data for latest data.

but can we have the active status for below  highlighted records, fetch the active status instead of block status which occurred in march

Also one more requirement missed to mention earlier one table should give all status information and one table should populated active status with latest updated

user CRMID Status last updated
1 111 Active 2-Jan
2 111 Active 4-Feb
3 123 Active 2-Feb
4 125 Blocked 2-Feb
5 122 Active 7-Feb
6 122 Blocked 2-Mar
vinieme12
Champion III
Champion III

 

Main:
Load 
User,CRMID,Status,[lastUpdated]
From YourSource;



Inner Keep(Main)
Load  CRMID,alt(lastActive,latest) as lastUpdated
;
Load 
CRMID
,max(if(Status='Active',lastUpdated,null())) as lastActive
,max(lastUpdated)) as latest
Resident Main
Group by CRMID;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
kvr9
Creator
Creator
Author

Getting an issue with max date 

when converted the date the max number is picking as per number value and not actual value

kvr9_0-1654624217734.png

 

vinieme12
Champion III
Champion III

Oh yeah, we can just format it back to date

 

 

Main:

Load 

User,CRMID,Status,[lastUpdated]

From YourSource;

 

 

 

Inner Keep(Main)

Load CRMID,alt(lastActive,latest) as lastUpdated

;

Load 

CRMID

,Date(max(if(Status='Active',lastUpdated,null())) ) as lastActive

,Date(max(lastUpdated)) as latest

Resident Main

Group by CRMID;

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
GnaneswarReddy
Contributor III
Contributor III

Do we really need Keep Here ??

vinieme12
Champion III
Champion III

You can use either Inner keep or inner join 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.