topic Re: Removing duplicates, getting latest value via multiple criteria in QlikView

Removing duplicates, getting latest value via multiple criteria

Tue, 08 Dec 2015 07:02:54 GMT

Hi there,

I have a set of data like this

Product No	Date Created	Record No	Date Altered	Reason
1	4/8/2014	RN0001	4/6/2014	Damaged
1	4/8/2014	RN0025	4/6/2014	AutoRepaired
1	4/1/2015	RN9090	4/11/2014	Damaged
2	5/9/2015	RN0002	3/9/2015	Creation
3	6/8/2015	RN0003	6/7/2015	Damaged
3	6/8/2015	RN0004	6/7/2015	AutoRepaired
3	10/10/2015	RN0006	9/9/2015	Damaged

I would like to remove duplicated based on product no, and get the latest value. Which should look like this

(This is to be done in load script)

Product No	Date Created	Record No	Date Altered	Reason
1	4/1/2015	RN9090	4/11/2014	Damaged
2	5/9/2015	RN0002	3/9/2015	Creation
3	10/10/2015	RN0006	9/9/2015	Damaged

Basically for each product no, i would like to obtain the data that has the LATEST DateCreated,RecordNo,DateAltered.

Thank you.

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Tue, 08 Dec 2015 16:58:45 GMT

Try this:

Table:

LOAD [Product No],

FirstSortedValue([Date Created], -[Date Created]) as [Date Created],

FirstSortedValue([Record No], -[Date Created]) as [Record No],

FirstSortedValue([Date Altered], -[Date Created]) as [Date Altered],

FirstSortedValue(Reason, -[Date Created]) as Reason

Group By [Product No];

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/1/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

Re: Removing duplicates, getting latest value via multiple criteria

Wed, 09 Dec 2015 01:23:01 GMT

Hi i used resident table, and your code, the result is that it removed all records that have duplicate, leaving only product code 2 behind when i tried searching for it.

This data set is only a small portion of my data, my real data has around 100k+ but the concept is exactly the same.

---------------------------

I tried your code, i know why it disappears, i was looking at a set of products, that are of all the same values for every field except for recordno.

Is it possible for it to remove all duplicates, and seeing if say we have this

This is the example set of data that disappeared.

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/8/2014, RN9090, 4/6/2014, Damaged

Because the code locks it to datecreated, hence i can understand why it would disappear as all the datecreated are the same.

Is there a way for it to look at all 3 fields(datecreated, recordno and date altered)? For example

If all the 3 entries above for Date created are the same, it will look at record no, and pick the last record no.

If even the record no are all the same, then it will look at date altered and find the latest one.

in short is it possible for it to pick, the latest date created & last record no & latest date altered?

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Wed, 09 Dec 2015 18:24:49 GMT

Try this code:

Table:

LOAD [Product No],

FirstSortedValue([Date Created], -([Date Created]+RecNo())) as [Date Created],

FirstSortedValue([Record No], -([Date Created]+RecNo())) as [Record No],

FirstSortedValue([Date Altered], -([Date Created]+RecNo())) as [Date Altered],

FirstSortedValue(Reason, -([Date Created]+RecNo())) as Reason

Group By [Product No];

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/8/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

Re: Removing duplicates, getting latest value via multiple criteria

Thu, 10 Dec 2015 02:09:05 GMT

Hi do you mind explaining how does adding recno() help? Thanks so much.

Is there another way to solve this, i have another part coming up doing the same thing, And this time the field will be mixture of text and numbers like the field in RECORD NO. Will it be confused if a larger set of say 1 million rows (not sorted) involved?

Thank you.

Re: Removing duplicates, getting latest value via multiple criteria

tyagishaila — Thu, 10 Dec 2015 06:28:56 GMT

You can try it,

ProdMaxDate:

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/1/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

LatestDate:

inner join

Load [Product No],

Date(max([Date Created])) as [Date Created]

Resident ProdMaxDate

group by [Product No];

Re: Removing duplicates, getting latest value via multiple criteria

Thu, 10 Dec 2015 06:47:19 GMT

Hi as from previous top replies, this wont work because if

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 6/8/2015, RN0006, 9/9/2015, Damaged

This happens, then it wont get the correct one cause all the date created are the same.

Do you have one that takes max of datecreated, max of recordNo and max of date altered?

For example for cases like this,

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 6/8/2015, RN0006, 6/7/2015, Damaged

So is there a script that takes into account all possible scenarios?

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Thu, 10 Dec 2015 10:30:42 GMT

Did you try this already?

Table:

LOAD [Product No],

FirstSortedValue([Date Created], -([Date Created]+RecNo())) as [Date Created],

FirstSortedValue([Record No], -([Date Created]+RecNo())) as [Record No],

FirstSortedValue([Date Altered], -([Date Created]+RecNo())) as [Date Altered],

FirstSortedValue(Reason, -([Date Created]+RecNo())) as Reason

Group By [Product No];

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/8/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Thu, 10 Dec 2015 11:29:26 GMT

RecNo() is the order of your rows. So if you see it in table form, you will see this:

Product No	Date Created	Record No	Date Altered	Reason	RecNo
1	4/8/2014	RN0001	4/6/2014	Damaged	1
1	4/8/2014	RN0025	4/6/2014	AutoRepaired	2
1	4/1/2015	RN9090	4/11/2014	Damaged	3
2	5/9/2015	RN0002	3/9/2015	Creation	4
3	6/8/2015	RN0003	6/7/2015	Damaged	5
3	6/8/2015	RN0004	6/7/2015	AutoRepaired	6
3	10/10/2015	RN0006	9/9/2015	Damaged	7

So I am sorting by [Date Created] + RecNo(). So when they are all the same the one with the highest RecNo() row will be selected.

Re: Removing duplicates, getting latest value via multiple criteria

Fri, 11 Dec 2015 16:14:42 GMT

Hi SunnyT,

Yes i did try on a small set of my data and it works, Not sure if it is the same on larger scale,

I just would like to understand something. I understand what you explained.

Product No	Date Created	Record No	Date Altered	Reason	RecNo
1	4/8/2014	RN0001	4/6/2014	Damaged	1
1	4/8/2014	RN0025	4/6/2014	AutoRepaired	2
1	4/1/2015	RN9090	4/11/2014	Damaged	3
2	5/9/2015	RN0002	3/9/2015	Creation	4
3	6/8/2015	RN0003	6/7/2015	Damaged	5
3	6/8/2015	RN0004	6/7/2015	AutoRepaired	6
3	10/10/2015	RN0006	9/9/2015	Damaged	7

The data above is already pretty much sorted.

What happens if the data is like this?

Product No	Date Created	Record No	Date Altered	Reason
1	4/8/2014	RN0001	4/6/2014	Damaged
1	4/8/2014	RN0095	4/6/2014	AutoRepaired
1	4/8/2015	RN0035	4/11/2014	Damaged
3	6/8/2015	RN0008	6/7/2015	Damaged
3	6/8/2015	RN0004	6/7/2015	AutoRepaired
3	6/8/2015	RN0006	9/9/2015	Damaged

This set of data is unsorted, as you can see the latest record no is the 2nd row for product no 1, and for product no 3, the latest record no is the 4th row.

How would your script handle this as we didnt mention how the data of record no and date altered will be sorted.

Does firstsortedvalue sort unspecified columns(since recordno and date altered are not mentioned)

Thank you so much SunnyT, Appreciate all your replies as it really improved my learning.

Oh another ques, Is it possible to do [Date Created]+[Record No]+[Date Altered] instead of [Date Created] + RecNo() ?

I tried it, it didnt work of course.

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Fri, 11 Dec 2015 17:04:09 GMT

Record No right now is a text field. You will need to convert into a number field, May be like this:

KeepChar([Record No], '0123456789') * 100/100

May be this code:

Table:

LOAD [Product No],

FirstSortedValue([Date Created], -([Date Created]+Check)) as [Date Created],

FirstSortedValue([Record No], -([Date Created]+Check)) as [Record No],

FirstSortedValue([Date Altered], -([Date Created]+Check)) as [Date Altered],

FirstSortedValue(Reason, -([Date Created]+Check)) as Reason

Group By [Product No];

LOAD *,

KeepChar([Record No], '0123456789') * 100/100 as Check;

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2015, RN0001, 4/6/2014, Damaged //Was given as 4/8/2014

1, 4/8/2015, RN0095, 4/6/2014, AutoRepaired //Was given as 4/8/2014

1, 4/8/2015, RN0035, 4/11/2014, Damaged

3, 6/8/2015, RN0008, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 6/8/2015, RN0006, 9/9/2015, Damaged

];

Change the data above (in red) as it seems to me that they will be on the same dates. Let me know if that isn't true

Re: Removing duplicates, getting latest value via multiple criteria

Fri, 11 Dec 2015 17:54:15 GMT

Hi SunnyT,

Thank you for the prompt reply.

As for whether they will be on the same dates, the thing is, this example is just like 6 records out of 1 million records that i have, and there are some records that have the same date created, but diff record no, some have same date created and diff date altered but of course diff record no. So i am trying to cover the possibilities here as i can't go through all 1 million records 1 by 1.

Some records no are like CNB1029-102, some are RN10203, so im looking for one formula that works for all, I thought of converting all to numbers and find the largest one, but i always fail to do so using Num().

Using keepchar might face some issue as mentioned records no format are not the same for each product, But however, for each product they will be in the same format.

For example if product 1 its record No is RNxxxx, it will always be like that.

For product say n, if its CNB1029-202, it will always be in that format, just the number changing.

Just an idea, remember the previous solution you posted? the one using RecNo()?
What if i sort my table first using loadscript, by datecreated, recordno, then date altered. That way, will it ever go wrong?

Thank you so much for your assistance, been trying to solve this problem for quite a while now.

Re: Removing duplicates, getting latest value via multiple criteria

sunny_talwar — Fri, 11 Dec 2015 18:03:17 GMT

If the data can be sorted, I would encourage you to do that and then use RecNo() function.

Re: Removing duplicates, getting latest value via multiple criteria

maxgro — Fri, 11 Dec 2015 18:32:36 GMT

Maybe with a where peek; you can change the order by as you want (ascending/descending on 2, 3, n fields)

Or I'm missing something?

Table:

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/8/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

NewTable:

NoConcatenate load *

Resident Table

Where [Product No]<>Peek('Product No')

order by [Product No], [Date Created] desc, [Record No] desc, [Date Altered] desc;

DROP Table Table;

Re: Removing duplicates, getting latest value via multiple criteria

Fri, 11 Dec 2015 18:45:11 GMT

Hi SunnyT,

It is a tricky situation considering different variables affecting this.

As i rmb, RecNo() takes data from the sorted loaded table or the original table?
if from the original table then there is no point sorting my data in a loadscript section beforehand?

basically in SAS, it would be like group by product id,

and if last.datecreated and last.recordno and last.datealtered.

How do i do the same here. hmmmmmmmmmmmmmm

Re: Removing duplicates, getting latest value via multiple criteria

Fri, 11 Dec 2015 18:46:57 GMT

Hi maxgro, do you mind explaining this further?

especially the Where [Product No]<>Peek('Product No')

thank you.

Re: Removing duplicates, getting latest value via multiple criteria

maxgro — Fri, 11 Dec 2015 19:34:51 GMT

Peek('Product No') is the last record read (I copied the QlikView help, below).

With

Where [Product No]<>Peek('Product No')

you get only the records with a different product no, one record for product no 1, one for 2, one for 3.

The order by is needed to choose the first record by product. Change the order by and you have a different first record. The product no must always be the first field in the order by.

The resident is needed because in Qlik you can only sort in a resident load.

The noconcatenate is needed because the fields are the same and I don't want an automatic concatenate.

You can play removing the where peek and adding the peek(....) as a new field.

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Examples:

peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current internal table.

peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current internal table.

peek( 'Sales', 0, 'Tab1' )
returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.

Re: Removing duplicates, getting latest value via multiple criteria

Fri, 11 Dec 2015 19:44:54 GMT

Hi maxgro, thanks for your explaination.

If thats the case, its true i will only get 1 record per product no, but how do i know which one to choose if say i have 3 product no 1, then diff date created, diff record no and diff date altered similar to the table of data above, Do note i have around 1 million records.

Sorry im relatively new to qlikview, do you mind providing a short example?
There are problems i highlighted to Sunnyindia in the above post as well.

So the idea here is that, you sort desc, and pick the first record for each product no right?

How do i get it to read the first record? Since i don't know how many records there are per productno, there can be 1, 2 ,...n

Thank you

Re: Removing duplicates, getting latest value via multiple criteria

maxgro — Fri, 11 Dec 2015 20:14:45 GMT

You choose the record with the order by

With this table

Table:

LOAD * INLINE [

Product No, Date Created, Record No, Date Altered, Reason

1, 4/8/2014, RN0001, 4/6/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/1/2015, RN9090, 4/11/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 6/8/2015, RN0003, 6/7/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 10/10/2015, RN0006, 9/9/2015, Damaged

];

if you want the last (bigger) product by DateCreated,RecordNo,DateAltered.

add an order by product, date created desc, record no desc, date altered desc

NewTable:

NoConcatenate load *

Resident Table

Where [Product No]<>Peek('Product No')

order by [Product No], [Date Created] desc, [Record No] desc, [Date Altered] desc;

DROP Table Table;

Qlik will read the record in this order

The records with product no <> previous product no (the where clause) are the records in bold

1, 4/1/2015, RN9090, 4/11/2014, Damaged

1, 4/8/2014, RN0025, 4/6/2014, AutoRepaired

1, 4/8/2014, RN0001, 4/6/2014, Damaged

2, 5/9/2015, RN0002, 3/9/2015, Creation

3, 10/10/2015, RN0006, 9/9/2015, Damaged

3, 6/8/2015, RN0004, 6/7/2015, AutoRepaired

3, 6/8/2015, RN0003, 6/7/2015, Damaged

Do you agree?

If you have different data and / or questions, post them.

Re: Removing duplicates, getting latest value via multiple criteria

maxgro — Sat, 12 Dec 2015 07:12:36 GMT

another example, in this one I want the oldest date altered, first rec no, ; you can see I changed the fields order in order by and replace descending with ascending (asc is the default, no need to specify)

NewTable:

NoConcatenate load *

Resident Table

Where [Product No]<>Peek('Product No')

order by [Product No], [Date Altered], [Record No], [Date Created];