Hi,
maybe I was not obvious,
I've well understood id being joined,
here is an example :
table :
table test :
| group | id | score |
| A | 1 | 5 |
| B | 1 | 3 |
| C | 1 | 1 |
| A | 2 | 2 |
| B | 2 | 3 |
| B | 3 | 4 |
| A | 4 | 4 |
| C | 4 | 2 |
| A | 5 | 2 |
| B | 5 | 2 |
| C | 5 | 1 |
tempid:
load distinct id resident test;
tempgroup:
load distinct group resident test;
outer join (tempid) load group resident tempgroup;
you get :
1 A
1 B
1 C
2 A
2 B
2 C
etc
left join (tempid) load id,group ,score resident test;
you get new column with your value known
temp:
noconcatenate load id,group, if(isnull(score),'N/A',score) as score resident tempid;
drop table test;
drop table tempid;
you get now N/A if row A,B or C not exist in generic table
regards
