Unique instance of values

[#1]

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.

[#2]

Hi

Try this expression:

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

[#3]

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?

[#4]

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.

[#5]

It doesn't work... 😞

[#6 · Accepted solution]

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

See attached.

[#7]

Thanks a lot!!!