topic Distinct values in straight table in QlikView

Distinct values in straight table

Mon, 13 Jun 2011 14:01:13 GMT

I'm trying to create a straight table that contains three fields. Lets call them A, B and C.

Field A is the important one. I need to have all the different values of A representated in a straight table. The problem is that I can only have it displayed once. Values in field A can occur several times with different values in field B and C. It doesn't matter which of the values in field B and C that is chosen.

Example:

Source file

A B C

-----------

1 F G

1 D H

2 H U

3 J P

3 K E

How it needs to look

A B C

-----------

1 F G

2 H U

3 K E

I started out by having A in the dimension and adding in a dummy expression of 1. I then added in B and C as expressions. By doing this I get all the different values in A represented only once but where A has different values corresponding in B and C, B and C is displayed as -.

Any ideas?

Re: Distinct values in straight table

Mon, 13 Jun 2011 14:49:19 GMT

try this:

TMP:

LOAD * INLINE [

A, B, C

1, F, G

1, D, H

2, H, U

3, J, P

3, K, E

];

DATA:

LOAD LastValue(F2) as F22,

LastValue(F3) as F33,

RESIDENT TMP

GROUP BY F1;

DROP TABLE TMP;

Distinct values in straight table

Mon, 13 Jun 2011 19:32:06 GMT

It works!

I really can't thank you enough!

Re: Distinct values in straight table

Wed, 16 Jul 2014 11:50:04 GMT

Would you have a solution for obtaining the exact same result in a calculated dimension in a straught table?