Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In Data load Editor ,I want results as pictures. Please help me.
Hello @mistnh001 .
Try this:
/* 1) EXTRACT DATA*/
Set NullInterpret = '';
TABLE_A:
LOAD * inline [
A,B,C,D,E,AMOUNT,
'A1',,'C01',,'E02',10,
'A2','B01',,,'E01',20,
'A3',,,'D01','E03',10,
'A4',,,,'E04',20,
'A5','B01',,,'E01',30,
];
TABLE_B:
load * inline [
B_CODE, NAME,
'B01', 'B111',
'B02', 'B222'
];
Noconcatenate
TABLE_C:
LOAD * inline [
C_CODE, NAME,
'C01', 'C111',
'C02', 'C222'
];
Noconcatenate
TABLE_D:
LOAD * inline [
D_CODE, NAME,
'D01', 'D111',
'D02', 'D222'
];
Noconcatenate
TABLE_E:
LOAD * inline [
E_CODE, NAME,
'E01', 'E111',
'E02', 'E222',
'E03', 'E333',
'E04', 'E444',
];
/* 2) TRANSFORM DATA*/
TABLE_RESULT:
LOAD B_CODE AS ALL,NAME
Resident TABLE_B;
Concatenate
LOAD C_CODE AS ALL,NAME
Resident TABLE_C;
Concatenate
LOAD D_CODE AS ALL,NAME
Resident TABLE_D;
Concatenate
LOAD E_CODE AS ALL,NAME
Resident TABLE_E;
Inner Join
LOAD A AS AA,
IF(ISNULL(B),IF(ISNULL(C),IF(ISNULL(D),E,D),C),B) AS ALL,
AMOUNT AS TOTAL
Resident TABLE_A;
DROP TABLE TABLE_A;
DROP TABLE TABLE_B;
DROP TABLE TABLE_C;
DROP TABLE TABLE_D;
DROP TABLE TABLE_E;
I hope it helps you.
Hello @mistnh001 .
Try this:
/* 1) EXTRACT DATA*/
Set NullInterpret = '';
TABLE_A:
LOAD * inline [
A,B,C,D,E,AMOUNT,
'A1',,'C01',,'E02',10,
'A2','B01',,,'E01',20,
'A3',,,'D01','E03',10,
'A4',,,,'E04',20,
'A5','B01',,,'E01',30,
];
TABLE_B:
load * inline [
B_CODE, NAME,
'B01', 'B111',
'B02', 'B222'
];
Noconcatenate
TABLE_C:
LOAD * inline [
C_CODE, NAME,
'C01', 'C111',
'C02', 'C222'
];
Noconcatenate
TABLE_D:
LOAD * inline [
D_CODE, NAME,
'D01', 'D111',
'D02', 'D222'
];
Noconcatenate
TABLE_E:
LOAD * inline [
E_CODE, NAME,
'E01', 'E111',
'E02', 'E222',
'E03', 'E333',
'E04', 'E444',
];
/* 2) TRANSFORM DATA*/
TABLE_RESULT:
LOAD B_CODE AS ALL,NAME
Resident TABLE_B;
Concatenate
LOAD C_CODE AS ALL,NAME
Resident TABLE_C;
Concatenate
LOAD D_CODE AS ALL,NAME
Resident TABLE_D;
Concatenate
LOAD E_CODE AS ALL,NAME
Resident TABLE_E;
Inner Join
LOAD A AS AA,
IF(ISNULL(B),IF(ISNULL(C),IF(ISNULL(D),E,D),C),B) AS ALL,
AMOUNT AS TOTAL
Resident TABLE_A;
DROP TABLE TABLE_A;
DROP TABLE TABLE_B;
DROP TABLE TABLE_C;
DROP TABLE TABLE_D;
DROP TABLE TABLE_E;
I hope it helps you.
thank you very much
Nice! Please mark this as resolved to close this question.