topic Re: Remove double entries in a table, based on value in QlikView

Remove double entries in a table, based on value

hansdevr — Mon, 22 Feb 2016 08:45:05 GMT

Although I still regard myself to be a newbie to QlikView, I thought this solution to a problem I had (and oh boy, did I wrestle with it..) is worthwhile posting.

Let's suppose you have a table, in which you have many entries for one Id. Sometimes, it's handy to "clean-up" that table and lose all records, except those with the highest value in a certain field (other than Id). This could be a date, an amount of $, anything.

I have done some experimenting with peek() function. Maybe this might come in handy for some of you.

Of course, you gurus out there who have a far more simple solution: I'm eager to learn about it!

I have attached the simple qvw file.

Best regards,

Hans

Re: Remove double entries in a table, based on value

ramasaisaksoft — Mon, 22 Feb 2016 09:15:31 GMT

Hi Hans,

You can do this easily by an expression in Straight table

exp need to be like below

=aggr((max(Val)),Id#).

Re: Remove double entries in a table, based on value

hansdevr — Mon, 22 Feb 2016 09:19:49 GMT

Yes, it would do that in a Straight table, but what I wanted, was to create a QVD, based on the original table. Displaying the values in a Straight table, was in my case not the aim, but reducing data.

Re: Remove double entries in a table, based on value

Chanty4u — Mon, 22 Feb 2016 09:20:21 GMT

LOAD *
WHERE NOT WildMatch(ur data)

Re: Remove double entries in a table, based on value

hansdevr — Mon, 22 Feb 2016 09:37:01 GMT

Not sure I understand... Care to elaborate?