topic Re: LOAD the latest record with duplicate LastUpdatedDates in QlikView

LOAD the latest record with duplicate LastUpdatedDates

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

QV12 SR3

Wondering the best way to LOAD the latest record when my dataset has this sort of thing...

Last Update Date

10/06/2016

22/03/2016

10/06/2016

03/01/2016

I need the latest record based on [Last Update Date] and then if they are equal (ID 1 & 3 above for example) then the highest ID value (ID is an autonumber) - which would leave me with just one record per email address.

Is it a case of preceding FirstSortedValues (if that's possible?) or is there a better way ?

Any pointers appreciated

Re: LOAD the latest record with duplicate LastUpdatedDates

narendiran — Wed, 20 Jul 2016 14:45:46 GMT

Use Incremental load, See the below link

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Re: LOAD the latest record with duplicate LastUpdatedDates

sunny_talwar — Wed, 20 Jul 2016 14:49:21 GMT

May be this:

Table:

LOAD Email,

FirstSortedValue(ID, -([Last Update Date]*1000+ID)) as ID,

FirstSortedValue([Last Update Date], -([Last Update Date]*1000+ID)) as [Last Update Date]

Group By Email;

LOAD * INLINE [

ID, Email, Last Update Date

1, me@somewhere.com, 10/06/2016

2, me@somewhere.com, 22/03/2016

3, me@somewhere.com, 10/06/2016

4, me@somewhere.com, 03/01/2016

];

Re: LOAD the latest record with duplicate LastUpdatedDates

Ponkaviyarasu — Wed, 20 Jul 2016 14:59:54 GMT

You can try this method

find max(Last update Date) in existing record then concatenate with new record where not exists(date) and not exists(Email)

Re: LOAD the latest record with duplicate LastUpdatedDates

Wed, 20 Jul 2016 15:04:23 GMT

Hi Paul,

You can write something like this:

//----------------------------------------------------------------------------

Test:

Load ID, Email, Date(Date) as LastUpdatedDate;

LOAD * INLINE [

ID, Email, Date

1, 123@abc.com, 42571

2, 123@abc.com, 42557

3, 123@abc.com, 42571

4, 123@abc.com, 42565

5, 123@abc.com, 42514

];

Test2:

Load

FirstSortedValue(ID,LastUpdatedDate) as ID

Resident

Test

Group By LastUpdatedDate;

left join

Load * resident Test;

Drop Table Test;

//----------------------------------------------------------------------------

You can sort the records from the sort menu of Table Box properties based on requirement.

Hope this helps.

Regards,

Sakir

Re: LOAD the latest record with duplicate LastUpdatedDates

santiago_respane — Wed, 20 Jul 2016 15:07:35 GMT

Hi,

first of all i'd say Sunny's suggestion is the most appropriate for this case (works perfect, less code, better performance, etc.).

Just to add another possible option here's a solution using inner join operations (more basic solution):

Table:

LOAD * INLINE [

ID,Email,Last Update Date

1,me@somewhere.com,10/06/2016

2,me@somewhere.com,22/03/2016

3,me@somewhere.com,10/06/2016

4,me@somewhere.com,03/01/2016

5,you@somewhere.com,03/01/2016

6,you@somewhere.com,03/08/2016

];

INNER JOIN(Table)

LOAD Email,MAX([Last Update Date]) as [Last Update Date] RESIDENT Table GROUP BY Email;

INNER JOIN(Table)

LOAD Email,MAX(ID) as ID RESIDENT Table GROUP BY Email;

Kind regards,

Re: LOAD the latest record with duplicate LastUpdatedDates

haymarketpaul — Wed, 20 Jul 2016 15:24:29 GMT

Perfect - Thank you

Re: LOAD the latest record with duplicate LastUpdatedDates

haymarketpaul — Fri, 22 Jul 2016 13:23:39 GMT

Have encountered an issue - in some cases both the Last Update Date and ID are identical (although some other data is different).

This seems to blank (NULL) the Last Update Date and ID fields for that email address.

Is there some way of ensuring that if they are identical that the field remains populated?

Re: LOAD the latest record with duplicate LastUpdatedDates

sunny_talwar — Fri, 22 Jul 2016 13:26:01 GMT

Try this:

Table:

LOAD Email,

FirstSortedValue(DISTINCT ID, -([Last Update Date]*1000+ID)) as ID,

FirstSortedValue(DISTINCT [Last Update Date], -([Last Update Date]*1000+ID)) as [Last Update Date]

Group By Email;

LOAD * INLINE [

ID, Email, Last Update Date

1, me@somewhere.com, 10/06/2016

2, me@somewhere.com, 22/03/2016

3, me@somewhere.com, 10/06/2016

4, me@somewhere.com, 03/01/2016

];

Re: LOAD the latest record with duplicate LastUpdatedDates

haymarketpaul — Fri, 22 Jul 2016 13:26:14 GMT

i got it......

DISTINCT

Re: LOAD the latest record with duplicate LastUpdatedDates

haymarketpaul — Fri, 22 Jul 2016 13:27:00 GMT

Thanks - saw it in another post you replied to

Re: LOAD the latest record with duplicate LastUpdatedDates

arnauayuso — Mon, 03 May 2021 11:17:00 GMT

Hi, thank you for the information, it is very useful. But I have a problem when using it in my script.

I have more dimensions.

If I put them in the Group by Clause, the duplicates appear againg, because there are dimensions with different values.

If i do not put them, Invalid expesion apears as an error.

Could you please help me with that?

I attach an example:

Table:

LOAD Email,

Number,

FirstSortedValue(ID, -([Last Update Date]*1000+ID)) as ID,

FirstSortedValue([Last Update Date], -([Last Update Date]*1000+ID)) as [Last Update Date]

Group By Email, Number;

LOAD * INLINE [
ID, Email, Number, Last Update Date
1, me@somewhere.com, 673, 10/06/2016
2, me@somewhere.com, 619, 22/03/2016
3, me@somewhere.com, 619, 10/06/2016
4, me@somewhere.com, 619, 03/01/2016
];

Result:

1, me@somewhere.com, 673, 10/06/2016

3, me@somewhere.com, 619, 10/06/2016