topic Unique instance of values in QlikView

Unique instance of values

Thu, 17 Jun 2010 10:15:18 GMT

Hi Everybody,

I'm new to qv and I'm trying to better understand how it works.

I have this table

ACCESS:
LOAD * INLINE [
User, Date, Hour, Other
A, 15, 13, 1
B, 15, 14, 1
B, 15, 15, 1
C, 15, 16, 1
A, 15, 17, 1
D, 15, 18, 1
A, 16, 19, 2
E, 16, 19, 2
];

I would like to show in a StraightTableBox the complete rows where the couple User-Date is unique

The result I expect is

User, Date, Hour, Other

C, 15, 16, 1
D, 15, 18, 1
A, 16, 19, 2
E, 16, 19, 2

I've used User and Date as Dimensions and =if(count(aggr(Date,User))=1,Hour) for Hour column, but adding Other field it doesn't work as expected.

Thanks in advance for your help.

Unique instance of values

Thu, 17 Jun 2010 10:35:01 GMT

Try this expression:

=IF(Count(DISTINCT User&Date)=1,Hour)

Unique instance of values

Thu, 17 Jun 2010 11:05:03 GMT

Hi Nigel,

thanks for your reply.

To show the column Hour it works fine, but when I try to show also the Other field, what happens is the following:

User Date Hour Other
A 15 - 1
A 16 19 2
B 15 - 1
C 15 16 1
D 15 18 1
E 16 19 2

I would like to be able to show all (and only) the table fields of the couple User-Date respecting the condition: where am I wrong?

Unique instance of values

Thu, 17 Jun 2010 11:12:26 GMT

How are you showing Other, is this a dimension or an expression, if its an expression you can use the same code as for Hour but change the value to Other.

If this doesn't help, can you attach your QVW so I can look at it.

Unique instance of values

Thu, 17 Jun 2010 11:33:33 GMT

It doesn't work... 😞

Unique instance of values

Thu, 17 Jun 2010 12:01:11 GMT

Like many things in QlikView I have over-complicated something that is very, very simple!

See attached.

Unique instance of values

Thu, 17 Jun 2010 12:17:02 GMT

Thanks a lot!!!