2 Replies Latest reply: Jul 27, 2016 2:54 PM by Christian Wolf

# Partial Transpose

Hello All,

I have a table

 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

I want to transpose T1,T2 and T3 only without changing there field names and doing it for respective C1.

Expected Output

 C1 C2 C3 T1 T2 T3 1 a 9 9 5 8 1 b 5 9 5 8 1 c 8 9 5 8 2 d 2 2 4 0 2 e 4 2 4 0 3 f 3 3 0 0 4 g 7 7 0 6 4 h 0 7 0 6 4 i 6 7 0 6 5 j 7 7 0 0

Thanking you all in anticipation

Regards

KP

• ###### Re: Partial Transpose

maybe this:

table:

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:

C1,

FirstSortedValue(T1,LINHA) AS TESTE1 Resident table Group BY C1;

Left Join(teste)

C1,

FirstSortedValue(T2,LINHA,2) AS TESTE2 Resident table Group BY C1;

Left Join(teste)

C1,

FirstSortedValue(T3,LINHA,3) AS TESTE3 Resident table Group BY C1;

Left Join(teste)

C1,

FirstSortedValue(T2,LINHA) AS TESTE21 Resident table Group BY C1;

Left Join(teste)

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;