topic Get Distinct ids in dimension in QlikView

Get Distinct ids in dimension

renjithpl — Thu, 02 Jun 2011 10:36:44 GMT

Hi all,

I have a field called [Case No], it has more duplicate Case numbers, i have selected that field in my dimension to bring a pivot table, its showing all duplicate ids, How will i filter out duplicate ids and show only rest in my dimension. ?

Regards

Ranjit

Get Distinct ids in dimension

Anonymous — Thu, 02 Jun 2011 12:33:06 GMT

Hi Ranjit,

Maybe this can help you.

Edit your dimension like this:

num(distinct [Case No])

Regards,

Henco

Get Distinct ids in dimension

renjithpl — Thu, 02 Jun 2011 12:55:20 GMT

No dint work...

Get Distinct ids in dimension

Thu, 02 Jun 2011 13:50:26 GMT

Ranji, not able to understand, I guessing out. Only(Distinct FieldName) . Check this helps you out or psot the sample data and explain us bit more.

- Sridhar

Get Distinct ids in dimension

renjithpl — Fri, 03 Jun 2011 07:20:20 GMT

Hi this is my pivot table, [Case No] is my dimension here, you can see some ids are duplicates,

like 6855, 6852,

i dont want to show duplicates,

= [Case No]

Case Name

Review

Rework

Grand Total

Training

6854

Prithvi Information Solutions Ltd

0.5

8.5

6855

Serenity Inn

6855

Serenity inn

6852

Multiverse

6852

Multiverse

6852

Multiverse

i want a table like below..

please look at the review and grand total it joins all the values too..

= [Case No]	Case Name	Review	Rework	QC	Grand Total
Training	Training	32	0	0	32
6854	Prithvi Information Solutions Ltd	8	0	0.5	8.5
6855	Serenity Inn	8	0	1	9
6855	Serenity inn	0	0	1	1
6852	Multiverse	14	0	1	15

Get Distinct ids in dimension

Anonymous — Fri, 03 Jun 2011 07:26:41 GMT

That's how I would expect it to show up. Is Review by any chance a dimension?

Could you upload a small example? That would give you a quick response from someone

Re: Get Distinct ids in dimension

Anonymous — Fri, 03 Jun 2011 07:35:39 GMT

EDIT: it looks like Review is a dimension and not a expression, is that right?

Maybe this can help you.

Use in the expression the aggr function.

For example: num(aggr(distinct Review, [Case No]))

As Johannes said, it would be helpfull if you can share a small example with us.

Regards,

Henco

Get Distinct ids in dimension

renjithpl — Tue, 07 Jun 2011 06:50:51 GMT

HI i have attached my qvw file... in the report tab is my table... i want to show the unique Case no as my dimension, i dont mind if you suggest me any different ways...

Thanks