Removing Duplicate Data on Date

[mhmmd_srf]

Hi All,

I have raw data in excel. Where One Incident No is having multiple records.

I need to pull only one record for each Incident, based on Start Date. We need only the max start date for each records.

I am attaching one sample file, where we need to pull only row no 2 (yellow highlighted) and 5 (green highlighted).

Please help me how to do this grouping on Incident No and take Max Created Date.

Thanks,

Sarif

[tresesco · Accepted solution]

Try using firstsortedvalue(), like:

Load

          Number,

          FirstSortedValue([SLA Definition], -[Start Time]) as [SLA Definition],

          FirstSortedValue([Has Breached], -[Start Time]) as [Has Breached],

          ....

          Max([Start Time]) as [Start Time]

From <> Group By Number;

[mhmmd_srf]

Do we need to include all fields in same fashion?

[tresesco]

Yes, using firstsortedvalue(); whichever you want to consider in the final loaded app.

[balabhaskarqlik]

May be:

FirstSortedvalue(Number,-[Created Date]) as Dis_Inc_Number

[thannila]

Hi,

Try this below script:

Table:

Load

    Number,

    "SLA definition",

    "Has breach",

date(MakeDate(SubField(Date,'-',3),SubField(Date,'-',1),SubField(Date,'-',2))+time(Time,'hh:mm:ss'),'DD-MM-YYYY h:mm:ss') as Date;

LOAD

    Number,

    "SLA definition",

    "Has breach",

    SubField("Start time",' ',1) as Date,

    SubField("Start time",' ',2) as Time

FROM [lib://s/Book2.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Load

Number&date(Max(Date),'DD-MM-YYYY h:mm:ss') as ExsistNumber,

Max(Date) as "Start time"

Resident Table

Group by Number;

FinalTable:

NoConcatenate

load * Resident Table where Exists(ExsistNumber,Number&date(Date,'DD-MM-YYYY h:mm:ss'));

Drop Table Table;

You can also add the additional fields if you want.