topic Re: Creating new column from already existing columns in QlikView

Creating new column from already existing columns

Tue, 19 Jan 2016 14:17:37 GMT

I have 3 columns as Below

A B C

1 2 4

2 1 3

3 5 1

I want to create 2 columns from the above 3 columns like below.

NewColumn Value

A 6

B 8

C 8

How can I acheive this ?

Re: Creating new column from already existing columns

sunny_talwar — Tue, 19 Jan 2016 15:28:23 GMT

May be like this:

Table:

LOAD * Inline [

A, B, C

1, 2, 4

2, 1, 3

3, 5, 1

];

FinalTable:

LOAD Sum(A) as Value,

'A' as NewColumn

Resident Table;

Concatenate(FinalTable)

LOAD Sum(B) as Value,

'B' as NewColumn

Resident Table;

Concatenate(FinalTable)

LOAD Sum(C) as Value,

'C' as NewColumn

Resident Table;

DROP Table Table;

Re: Creating new column from already existing columns

settu_periasamy — Tue, 19 Jan 2016 16:02:33 GMT

One more solution may be

T1:
LOAD '' as dummy,*;
LOAD * INLINE [
    A, B, C
    1, 2, 4
    2, 1, 3
    3, 5, 1
];


T2:
CrossTable(Field, Data)
LOAD dummy, 
     A, 
     B, 
     C
Resident T1;


DROP Table T1;
DROP Field dummy;


NoConcatenate
Final:
LOAD Field,sum(Data) as Data Resident T2 Group by Field;


DROP Table T2;

Re: Creating new column from already existing columns

sunny_talwar — Tue, 19 Jan 2016 16:15:14 GMT

I like this more than my solution. Thanks for sharing this Settu

Re: Creating new column from already existing columns

settu_periasamy — Tue, 19 Jan 2016 16:17:01 GMT

Thanks sunindia‌