right join problem

[ekuijper]

Hello all,

I have a problem with a selection in my script.

My original table looks like this:

ID1	ID2	VALUE
1	10	4
1	10	7
2	18
3	33	67
3	33	5

I would like to find the lowest value for every ID2 and also show the corresponding ID1, so I made the following script:

TABLE1:
load * INLINE [
ID1, ID2, VALUE,
1, 10, 4
1, 10, 7
2, 18,
3, 33, 67
3, 33, 5
];

RIGHT JOIN(TABLE1)
LOAD
ID2,
MIN(VALUE) AS VALUE
RESIDENT TABLE1 GROUP BY ID2;

The result is:

This is not as desired, ID1 is missing in the line where there is no value.

Can someone please tell me why ID1 is missing (I expected it to be 2) and how I can solve this?

thanks,

Edwin

[Anonymous]

Hi Edwin, group by ID1 and ID2, regards!

[avinashelite]

I think because of the null value your facing this issue, try to populate the 0 in null value places and check again that should work

[maxgro · Accepted solution]

another solution could be

TABLE1:

load * INLINE [

ID1, ID2, VALUE,

1, 10, 4

1, 10, 7

2, 18,

3, 33, 67

3, 33, 5

];

T2:

NoConcatenate

load

  ID1, ID2, VALUE

Resident TABLE1

Where ID1 <> Peek('ID1')

order by ID1, ID2, VALUE;

drop Table TABLE1;

[tamilarasu]

Hi Edwin,

TABLE1:
load * INLINE [
ID1, ID2, VALUE,
1, 10, 4
1, 10, 7
2, 18,
3, 12, 2
3, 12, 67
3, 33, 5
];

Final:
NoConcatenate
LOAD ID1,
ID2,
FirstSortedValue(VALUE,VALUE) AS VALUE
RESIDENT TABLE1 GROUP BY ID1, ID2;

DROP Table TABLE1;

[ekuijper]

Hi Tamil,

Thanks, this works great

Edwin

[ekuijper]

Hi Massimo,

Thanks for this solution, I have two working options now.

Edwin