Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
i have four columns (A,B,C,D) in the same table which contains zeros and null values in the A,B,C (tables) i need to replace only null
value in ABC columns from D column(any value)
i think i need to use IF condition to replace the null values
A | B | C | D |
123 | 221 | - | 321 |
124 | 222 | 0 | 322 |
0 | 223 | 0 | 323 |
126 | 224 | - | 324 |
127 | - | 234 | 325 |
0 | 0 | 11 | 326 |
- | - | - | 327 |
- | 0 | - | 328 |
Regards
Pardhu
A, B , C D are tables or fields in one Table? it's confusing
nope all are separate tables
provide more details.
Regards
You need to replace the null values in A,B,C from table D??
if so try like this
if(len(trim(A))=0,D,A) as A,
if(len(trim(B))=0,D,B) as B,
if(len(trim(C))=0,D,C) as C
This might also work:
LOAD Alt(A, D) as A,
Alt(B, D) as D,
Alt(C, D) as C,
D
FROM...
Hi Saradhi,
Try like this
T1:
load * Inline [
A, B, C, D
123 ,221, -, 321
124 ,222, 0, 322
0 ,223, 0, 323
126 ,224, -, 324
127, -, 234,325
0 ,0 ,11 ,326
- ,- ,-, 327
-, 0, -, 328
];
NoConcatenate
T2:
load
Alt(A, D) as A,
Alt(B, D) as B,
Alt(C, D) as C,
D
Resident T1;
DROP Table T1;
Just an addition to others
if(isnull(A),D,A) as A
Similarly to all columns
This will work only if the qvd contains the null values , if it contains spaces then as others suggested
you should take if(len(Trim(field))=0,D,A) as A
alt or if condition the to solve the problem
Hi,
You can also try Alt() like below, it will return second parameter value if first parameter value is null.
TableName:
LOAD Alt(A, D) as A,
Alt(B, D) as B,
Alt(C, D) as C,
D
FROM DataSource;
Hope this helps you.
Regards,
Jagan.