Is there anyway to Use Group by function in AEP Segment builder

[gajendrahs1]

We have a scenario where we want to filter all those customer profiles in AEP based on comparison of an attribute(Not on Primary key) between different events present for those profiles.

This can be done through SQL group by function however we want to achieve through AEP segment is there way to accomplish?

[nnakirikanti]

@gajendrahs1 Please do elaborate more on the requirement before providing recommendation.

[gajendrahs1]

@nnakirikanti 

Membership schema (Record class)

Attribute : EID which is Primary key

Event class schema Attributes:

Claim number (not a primary key)

fill date 

days supply

claim status (value can be eligible or ineligible)

Note : Every profile can have multiple claim events at the same time with eligible or ineligible status and claim number can be different or same

Requirement : Need to create segment with following conditions

1.We need latest events with claim status as eligible.

2. If we have multiple events with different claim numbers then we have to look for only those events with claim number which has latest claim status as eligible

3. fill date should be 10 to 20 days ago

4. Days supply should be 30 to 45 days range

For example : 

EID	claim number	claim status	days supply	fill date	segment qualification
123	101	eligible	15	35	Yes
123	101	Ineligible	15	35	No
123	102	eligible	15	35	Yes
123	103	eligible	15	35	Yes
123	102	Ineligible	15	35	Yes (It qualified because of claim number 103 being still eligible and meeting criteria days supply and fill date)

[arpan-garg · Accepted solution]

Hi @gajendrahs1 - This requirement seems to be a complex one and not achievable via segmentation. I will recommend you to run a scheduled query with this logic and later add a custom attribute on a profile with the required result. Later you can create segmentation using the custom attribute.

Thanks,

Arpan

[Arijit_Ghosh1]

Hi @gajendrahs1 

Please refer to the below documentation if it helps -->

Segment Builder UI Guide | Adobe Experience Platform

Regards,

Arijit Ghosh