topic Re: Regarding duplicate values in QlikView

Regarding duplicate values

Mon, 17 Nov 2014 20:13:35 GMT

Hello Friends,

I appreciate your help.

In the below script, I have to display only amount2 from table2 for any key values. But if amount2 not present then amount 1 from table1. Unfortunately I am getting both amount1 and amount2 when both are greater than zero for any key. I have attached my sample application, kindly share your ideas.

Kind regards,

Kiru

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:19:30 GMT

Hi,

Did you mean to join your tables instead of concatenating them ?

Try to replace the "Concatenate" with "Join" and check if this will get the results you wanted.

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:21:36 GMT

Hi,

I tried join but still I am getting the duplicate values.

I appreciate your time.

Kiru

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:31:22 GMT

Ok,

In table2, there's no key with value 2, so you want to consider the amount1 from table1 which is 500.

for key 443567, it is present in both tables, the amount is the same 429.

for key 1936, it's also present in both tables, but the priority is for table2 where the amount is 2971.

So amount2 is the field you need to consider in your case, not the amount1.

If not, can you write down the expected result so we can figure out how to modify the script.

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:44:06 GMT

Hi,

Thanks again for your reply.

You exactly got my requirement. Everything is working good except when both tables has different amounts for a key.

In this case 1936.

So when I try to show the key and new_amount in a table box. For key 1936 it is displaying both the amounts which is 2834 and 2971. But as per my requirement I need only to display 2971 and I don't want to show 2834 in my final_amount.

I hope you understand but kindly let me know if you didn't got it.

Kind regards,

Kiru

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:49:16 GMT

Exactly,

if you don't need the field amount1 in your table "link", then you can drop it, or just comment it in the last load.

in your app, i think you only need to use the field amount2 from the table "link" since it represents the final amount to consider between the initial amount1 and amount2.

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:52:34 GMT

Yes, I did that but still amount2 is showing both the values. I have attached the application for your reference.

Kindly let me know for any other possibilities.

Kiru

Re: Regarding duplicate values

Mon, 17 Nov 2014 20:57:26 GMT

Use join instead of concatenate, and that's it.

Re: Regarding duplicate values

Mon, 17 Nov 2014 21:25:46 GMT

Hi,

My sincere thank you but I think I can't use join because, it is taking a lot of time to reload my original application.

Regards,

Kiru

Re: Re: Regarding duplicate values

Mon, 17 Nov 2014 22:21:08 GMT

I see,

How about keeping both of your tables and getting the final amount on the chart ?

Check the file attached (I added a straight table that checks and calculates the final amount to consider).

Re: Regarding duplicate values

Mon, 17 Nov 2014 22:56:31 GMT

Hi QlikCommunity, I know that QlikView table boxes filter duplicated records in such a way that if two records are identical on all points listed in the table box, the table box will only show one line for that record like so:

Records/Values in database might be like this:

User ID Date Minutes Uploaded

123 12/13/2013 50 12/14/2013

123 12/13/2013 50 12/15/2013

(Uploaded field isn't in my qvds, but I am guessing something like it is hiding somewhere or snuck through somehow.)

QlikView TableBox shows this

User ID Date Minutes

123 12/13/2013 50

QlikView Straight Chart shows this

User ID Date Minutes

123 12/13/2013 100

Now, how do I make a QlikView Straight Chart that sums in such a way as to only count duplicated records once? Mine is doing this currently for one group that had some kind of technical issue and might have been reloaded or might have something about two processes feeding the same data table or something. The issue seems to be persistent and consistent in causing my sums to show double the minutes actually logged. I'm hoping for some simple way to make my formula spin its sums of whatever QlikView is doing itself to handle the duplicate records. Thanks!