Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
it might be little lengthy...
PFA file there are basically 4 tables TABLE A consist of common field which is able to link to remaining 3 tables
TABLE A.SMS_ID=TABLE B.MSG_ID
then TABLE C and D are getting concatenate and after that joining to TABLE A
DATA:
LOAD
TABLE C
....;
CONCATENATE
TABLE D
....;
TABLE A:
...;
LEFT JOIN (TABLE A)
RESIDENT DATA;
till here am getting the every thing properly but in my requirement we need to show all dates which are coming from 3 different table shoud come into single table i have tried it thorugh same filed name but it is giving me Synthetic key.
then i have tried belwo logic;
if(ISNULL(CH.DATE1),DATE1) as NEW_DATE1,
if(ISNULL(CH.DATE2),DATE2) as NEW_DATE2,
but am getting null values
Pls suggest.
Regards,
KK
Hi, Karim!
Can you post a picture from a table viewer, please? (Ctrl+T)
The data in TABLE C and TABLE D are exactly same, any reason for that. When you concatenate you will have duplicate records? any reason for having same data in both the tables or its just a mistake?
This generates your output but really don't understand much about the data sample.
[TABLE A]:
Load STATUS, SMS_ID inline [
SR_ID, STATUS, SMS_ID
11, SUCCESS, 6
12, FAILED, 8
13, OPEN, 7
22, SUCCESS, 9
72, SUCCESS, 2 ];
left join([TABLE A])
Load MSG_ID as SMS_ID, CH.DATE1 as DATE1, CH.DATE2 as DATE2, STATUS inline [
MSG_ID, CH.DATE1, CH.DATE2, STATUS
6, 8/10/2015, 12/11/2015, SUCCESS
8, 8/11/2015, 12/12/2015, FAILED
7, 8/12/2015, 12/13/2015, OPEN
9, 8/13/2015, 12/14/2015, SUCCESS
2, 8/14/2015, 12/15/2015, SUCCESS ];
[TABLE C]:
Load * inline [
SR_ID DATE1 DATE2 STATUS
11 10/10/2016 11/11/2016 SUCCESS
12 10/11/2016 11/12/2016 FAILED
13 10/12/2016 11/13/2016 OPEN
22 10/13/2016 11/14/2016 SUCCESS
72 10/14/2016 11/15/2016 SUCCESS ](delimiter is spaces);
Concatenate([TABLE C])
Load * inline [
SR_ID DATE1 DATE2 STATUS
11 10/10/2016 11/11/2016 SUCCESS
12 10/11/2016 11/12/2016 FAILED
13 10/12/2016 11/13/2016 OPEN
22 10/13/2016 11/14/2016 SUCCESS
72 10/14/2016 11/15/2016 SUCCESS ](delimiter is spaces);
Concatenate([TABLE A])
Load *
RESIDENT [TABLE C];
Drop table [TABLE C];
TNX