topic How do i remove duplicates on this load in QlikView

How do i remove duplicates on this load

Anonymous — Thu, 30 Mar 2017 20:16:14 GMT

hello

below is a table i have by concatenating two temp tables together.

but i get duplicate rows. like below.. (3 lines for bisqe-7335-0001707-007335-hash)

how can i do it where i would only get one.. and preferably the one that has a value in the http://148 section?

thanks

Re: How do i remove duplicates on this load

tschullo — Thu, 30 Mar 2017 21:30:47 GMT

Load Field1,

Field2,

Field3,

Field4,

firstsortedvalue(Field5, Len(Field5)*RowNo()+RowNo(),-1) as URL

Resident Table1

group by Field1,Field2, Field3, Field4;

Len(Field5)*RowNo()+RowNo() will generate a unique value for each row within the matching set.

-1 says sort in descending order, so largest number will be returned, likely one that has a URL

Re: How do i remove duplicates on this load

Anonymous — Fri, 31 Mar 2017 18:14:52 GMT

i keep getting an error

join(FabImages)

load

bcItemCode as BCItemCode,

BCColorCode as BCColorcode,

Swatch as swlocation,

firstsortedvalue(Swatch,Len(Swatch)*RowNo()+RowNo(),-1) as swlocation2,

hash128(bcItemCode,BCColorCode) as fabcolor

resident temp14;

Re: How do i remove duplicates on this load

tschullo — Fri, 31 Mar 2017 18:21:34 GMT

You must have the Group By statement or it will not work

I'm not sure what you are joining to or on

Your group by needs to be by all your columns except the one you are sorting

and if you are pulling Swatch as swlocation AND sorting by it, it may not like that.

Re: How do i remove duplicates on this load

Anonymous — Fri, 31 Mar 2017 20:25:37 GMT

yup.. i needed the grouping..

it works.. it narrowed it down.. but for some reason im still getting blanks.. even if i have one with a link

so close..

i tried grouping by the hash and it doesnt work..

join(FabImages)

load

bcItemCode as BCItemCode,

BCColorCode as BCColorcode,

//Swatch as swlocation,

firstsortedvalue(Swatch,Len(Swatch)*RowNo()+RowNo(),-1) as swlocation,

hash128(bcItemCode,BCColorCode) as fabcolor

resident temp14

group by bcItemCode,BCColorCode;

Re: How do i remove duplicates on this load

tschullo — Fri, 31 Mar 2017 20:29:56 GMT

So the only bummer is that if you have two winners, i.e. your sort value is the same for your two top records, it returns NULL.

So I gave you a start at generating a unique sort value, but you may need to test out and display those values so you can see if you are getting dupes and if so maybe come up with a better formula for a sort value.

Re: How do i remove duplicates on this load

tschullo — Fri, 31 Mar 2017 20:34:19 GMT

I think what I've done in the past is tantamount to "Len(string).rowNo()"

so:

0.1

0.2

5.3

5.4

22.5

23.6

23.7

So you will always have a unique winner, in this case 23.7 - I'd have to go look at how I built that float.

Re: How do i remove duplicates on this load

Anonymous — Fri, 31 Mar 2017 21:10:09 GMT

i think the issue is null is there.. and its not being sorted.. i did the show value thing..

all the dups are null.. and the other record is max value from formula... so im not sure how to fix this

or i need to do it in another temp and do it from there

Re: How do i remove duplicates on this load

stonecold111 — Sat, 01 Apr 2017 08:11:15 GMT

replace null values with 0's . try len(trim(field)=0,'0',field) and then sort

you can also use expression in place of field