maybe this:
table:
LOAD *, RowNo() AS LINHA;
LOAD * Inline [
C1, C2, C3, T1, T2, T3
1, a, 9, 9, 9, 9
1, b, 5, 5, 5, 5
1, c, 8, 8, 8, 8
2, d, 2, 2, 2, 0
2, e, 4, 4, 4, 0
3, f, 3, 3, 0, 0
4, g, 7, 7, 7, 7
4, h, 0, 0, 0, 0
4, i, 6, 6, 6, 6
5, j, 7, 7, 0, 0
];
teste:
LOAD
C1,
FirstSortedValue(T1,LINHA) AS TESTE1 Resident table Group BY C1;
Left Join(teste)
LOAD
C1,
FirstSortedValue(T2,LINHA,2) AS TESTE2 Resident table Group BY C1;
Left Join(teste)
LOAD
C1,
FirstSortedValue(T3,LINHA,3) AS TESTE3 Resident table Group BY C1;
Left Join(teste)
LOAD
C1,
FirstSortedValue(T2,LINHA) AS TESTE21 Resident table Group BY C1;
Left Join(teste)
LOAD
C1,
FirstSortedValue(T3,LINHA) AS TESTE31 Resident table Group BY C1;
Table1:
LOAD C1,TESTE1 as t1, TESTE2 as t2, TESTE3 as t3 Resident teste Where not IsNull(TESTE2) and not IsNull(TESTE3);
Concatenate(Table1)
LOAD C1,TESTE1 as t1, TESTE2 as t2, TESTE31 as t3 Resident teste Where not IsNull(TESTE2) and IsNull(TESTE3);
Concatenate(Table1)
LOAD C1,TESTE1 as t1, TESTE21 as t2, TESTE31 as t3 Resident teste Where IsNull(TESTE2) and IsNull(TESTE3);
Inner Join(Table1)
LOAD C1,C2,C3 Resident table; DROP Table table;DROP Table teste;
