Need to display unique records based on key and date

[Anonymous]

Hi ,

I have below table as sample data :

CampaignId	CapaignName	Status	SentDate	SentValue
101	ABC	Pending		0
101	ABC	Sent	09-04-2018	540
201	XYZ	Pending		0
301	PQR	Sent	09-04-2018	1020
301	PQR	Pending		0

I need ouput as below table :

CampaignId	CapaignName	Status	SentDate	SentValue
101	ABC	Sent	09-04-2018	540
201	XYZ	Pending		0
301	PQR	Sent	09-04-2018	1020

Thanks in advance,

Akshay

[MK_QSL]

Create a Straight Table

Dimension

CampaignId

CapaignName

Expression

IF(Max(TOTAL <CampaignId> SentDate),FirstSortedValue(Status, -SentDate),'Pending')

FirstSortedValue(SentDate, -SentDate)

SUM(SentValue)

[sasiparupudi1]

Try in script

Camp_Temp:

Load * Inline

[

CampaignId,CapaignName,Status,SentDate,SentValue

101,ABC,Pending, ,0

101,ABC,Sent,09-04-2018,540

201,XYZ,Pending, ,0

301,PQR,Sent,09-04-2018,1020

301,PQR,Pending,,0

];

Camp:

noconcatenate Load

CampaignId,CapaignName,Status,SentDate,SentValue

Resident

Camp_Temp

;

T22:

Load CampaignId, MaxString(SentDate) as SentDate,

1 as IsIncluded

Resident Camp_Temp

Group By

CampaignId;

drop Table Camp_Temp;

in the frontend you can use the flag to filter the records Sum({<IsIncluded={1}>}SentValue)

hth

Sasi