how to return 2 same names in one table separately?

[claudialet]

How do I return only  one of  the values to show up twice in a table ?

Here are 2 tables:              Here is requirement :

Colors     SortID                        Colors

red              1                                  red

red             2                                  blue

red            3                                 green

blue            4                             green

green           5

green            6

[Gysbert_Wassenaar]

Load DISTINCT Colors FROM ...source_table...;

[claudialet]

 No that didn't work.

The dual ( value, sort ID ) doesn't work either. 

I only need to show 'green' twice not the others

[miguelbraga · Accepted solution]

Hey there, 

You could do something like this:

Table:
LOAD * INLINE [
    Colors, SortID
    red, 1
    red, 2
    red, 3
    blue, 4
    green, 5
    green, 6
];

Table_Final:
NoConcatenate
LOAD Distinct Colors
Resident Table
Where Colors <> 'green';

left join

LOAD
 Colors,
 Max(SortID) as SortID
resident Table
Where Colors <> 'green'
Group by Colors
;

Concatenate

LOAD
 Colors,
 SortID
Resident Table
Where Colors = 'green';

Drop Table Table;

And the final result should be like this:

Refer to the attached file.

Best regards,

MB

[Vegar]

I would ask, what in the data indicates why a certain colour is to be displayed multiple times and other once? Is there a logical rule for this or is is always the same value/colour?

[claudialet]

it's the business requirement.

[Vegar]

You could use load distinct, but for one of the green values add a blank space at the end. This will leave you with two distinct green values 'Green' and 'Green '.

[claudialet]

Correction :

'Colors' and 'SortID' both come from one table .

Table1:
Colors
Sort Id