topic Re: Partial Transpose in QlikView

Partial Transpose

Anonymous — Wed, 27 Jul 2016 07:48:37 GMT

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

Re: Partial Transpose

Wed, 27 Jul 2016 13:51:36 GMT

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;

Re: Partial Transpose

cwolf — Wed, 27 Jul 2016 18:54:07 GMT

Hi Karunpreet,

look at

Creating a logic with multiple joins

It's the same!

regards

Christian

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

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

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

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

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

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