Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct values in straight table

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?

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

3 Replies
Not applicable
Author

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;

Not applicable
Author

It works!

I really can't thank you enough!

Not applicable
Author

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