Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aksharma
New Contributor

Need to display unique records based on key and date

Hi ,

I have below table as sample data :

     

CampaignIdCapaignNameStatusSentDateSentValue
101ABCPending0
101ABCSent09-04-2018540
201XYZPending0
301PQRSent09-04-20181020
301PQRPending

0

I need ouput as below table :

     

CampaignIdCapaignNameStatusSentDateSentValue
101ABCSent09-04-2018540
201XYZPending0
301PQRSent09-04-20181020

Thanks in advance,

Akshay

2 Replies

Re: Need to display unique records based on key and date

Create a Straight Table

Dimension

CampaignId

CapaignName


Expression

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

FirstSortedValue(SentDate, -SentDate)

SUM(SentValue)

sasiparupudi1
Honored Contributor III

Re: Need to display unique records based on key and date

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

Community Browser