Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

Group by is giving duplicate lines

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

 

Labels (5)
5 Replies
Or
MVP
MVP

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
Creator
Creator
Author

Hi ,

 

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

Or
MVP
MVP

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
Creator
Creator
Author

Hi,

 

Still getting duplicates

Or
MVP
MVP

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.