topic Re: Removing Duplicate Data on Date in QlikView

Removing Duplicate Data on Date

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Removing Duplicate Data on Date

tresB — Thu, 26 Jul 2018 08:38:12 GMT

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;

Re: Removing Duplicate Data on Date

mhmmd_srf — Thu, 26 Jul 2018 09:08:55 GMT

Do we need to include all fields in same fashion?

Re: Removing Duplicate Data on Date

tresB — Thu, 26 Jul 2018 09:10:40 GMT

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

Re: Removing Duplicate Data on Date

BalaBhaskar_Qlik — Thu, 26 Jul 2018 09:42:39 GMT

May be:

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

Re: Removing Duplicate Data on Date

thannila — Thu, 26 Jul 2018 12:31:51 GMT

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.