topic Re: remove duplicates while taking sum. in QlikView

remove duplicates while taking sum.

Mon, 13 Jun 2011 18:07:09 GMT

Hi All,

Hope you had a great weekend.

Here I am with a new set of problem.

I have attached an excel with this email with the data set.

The problem is

I have duplicate item Id. So how do I remove the duplicates?. My final aim is to total the Design - Create date and count the unique Item Id and then find the average.

But here because of the duplicate item ID, I also have extra amount in my total. How do i remove the duplicates and there influence in the total.

please help,

Thanks a lot,

Regards,

Bikash

remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 18:27:10 GMT

Do you need the dup data?
Why don´t you just LOAD DISTINCT from your excel file?

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 18:32:08 GMT

Hi Anjos,

Thanks for the solution.

But I am reading them from QVDs.

The problem actually started this way,

I had several Modified Date for the same "stage" of the Item in the data model. Stage,here are like pending stage, design stage etc.

But my user is actually using the last. modified date. So I loaded the max(Modified Date) with the Item Id, which suppossedly should have given me single records for each item, but now it seems that even max(modified date) is repeating.

Please suggest,

Thanks,

Bikash

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 18:39:55 GMT

LOAD
[ITEM ID],
MAX([Design -Create Date]) as MaxDesignDate
FROM [your file]
GROUP BY [ITEM ID];

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 18:45:10 GMT

Could you please explain,

What your code will do? I understand that it will pick the item and the maximum of the difference.

Because I don't have a field called Design-Create Date in the model.

I have made that field in this excel just to explain my problem.

I am sorry, if that lead you astray.

Bikash

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 18:51:59 GMT

This code will calculate the Maximum DesingCreate Date (This field is present in your excel file) for each ITEM ID.
LOAD
[ITEM ID],
MAX(Modified Date) as MaxModifiedDate

FROM [your file]
GROUP BY [ITEM ID];

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 19:13:49 GMT

But now I see that this Max(Modified Date) is also repeating.

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 19:19:20 GMT

Are you using GROUP BY modifier?
It seems weird to me.
Could you post a qvw with sample data?

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 19:20:30 GMT

No, I am using group by ITEM ID.

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 19:25:50 GMT

Are you using ou are you NOT using GROUP BY?
Could you post a qvw with sample data?

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 19:28:07 GMT

Yes, I am using the group by, this way,

Load

ITEM_ID,

max(MODIFIED_DATE) as MaxDate

resident (mytable)

Group by ITEM_ID ;

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 19:36:14 GMT

This code wouldn´t duplicate data.
Where do you see your duplicate data?
A Graph? Which Dimentions are you using in it?

Re: remove duplicates while taking sum.

Mon, 13 Jun 2011 19:37:22 GMT

I am seeing that in a table box. 😞

Re: remove duplicates while taking sum.

Clever_Anjos — Mon, 13 Jun 2011 19:40:22 GMT

I´m guessing you have MODIFIED_DATE in your table, right?
Remove it from your table just to check.
It would be easier if you upload a qvw

Re: remove duplicates while taking sum.

Clever_Anjos — Fri, 17 Jun 2011 21:41:40 GMT

Hello Bikash,
Is it working now?

Re: remove duplicates while taking sum.

Sat, 18 Jun 2011 00:29:56 GMT

Hey Clever Anjos,

Yes, that'w working now.

Regards,

Bikash

Re: remove duplicates while taking sum.

Clever_Anjos — Sat, 18 Jun 2011 14:12:21 GMT

That´s great news.
Please mark the topic as 'answered', so
Best regards