fetch latest record with multiple status

[kvr9]

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

[Richadam]

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

[vinieme12]

As below

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



Inner Keep(Main)
Load CRMID,max(lastUpdated) as lastUpdated
Resident Main
Group by CRMID;

[kvr9]

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]

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;

[kvr9]

Getting an issue with max date 

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

[vinieme12]

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;

[GnaneswarReddy]

Do we really need Keep Here ??

[vinieme12]

You can use either Inner keep or inner join