Distinct values in straight table

[#1]

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?

[#2 · Accepted solution]

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,

    F1

RESIDENT TMP

GROUP BY F1;

DROP TABLE TMP;

[#3]

It works!

I really can't thank you enough!

[#4]

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