Group by is giving duplicate lines

[kvr9]

Hi,

I have a scenario where the data is duplicated with group by.

Prod:

load * inline[

Country,Region, product, product type

India, Asia, Colgate, Paste

India, Asia, Maggi, Food

China, Asia, Colgate, Paste

China, Asia,pepsodent,Paste

UK,Europe, Closeup,Paste

Swiss, Europe, Maggi, Food

UK,Europe, Colgate, Paste

]

I want to calculate the count of productt ypes with a drill down like in a region-country-Producttype-Products

Prod_temp:

Load Country,Region, product, product type,

Count(distinct product type) typeofproducts

Resident Prod

Group by Country,Region, product, product type;

I'm getting duplicates lines for while calculating it 

Kindly suggest how to get the unique number in drill down

Thanks

[Or]

In this scenario, your group by contains all of the fields, so the result will just be the same number of lines and the count will always be 1 (or zero if product type is null). If you want to count the number of distinct product types, you need to remove product type from the Group By (and from the list of Load fields as well, of course).

[kvr9]

Hi ,

Yes, But at the same time I have to join this table with other table using a key generated using all fields.

[Or]

If you do that, you'll get duplication of your values. You *could* do it, but it doesn't seem to make a lot of sense. That said, it'd presumably look like this:

Load Country,Region, product, product type

From YourTable;

JOIN

Country,Region, product, count(distinct product type) as typeofproducts

From YourTable;

Group by Country,Region, product;

[kvr9]

Hi,

Still getting duplicates

[Or]

You will get duplicates by definition. You are requiring duplicates. There's no way around it. All you can do is give the correct count for these duplicates. There is no way to keep all four dimension levels detailed and *not* have one row for each of these combinations, so I'm not sure what exactly you're trying to do here.