Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've the below example data.
ID | Desc | Eq |
1 | ABC | 123 |
1 | DEF | 123 |
1 | GHI | 123 |
1 | JKL | 123 |
1 | MNO | 123 |
1 | PQR | 123 |
2 | 123 | |
3 | APP | 343 |
3 | ASS | 343 |
3 | OPP | 343 |
4 | 343 |
My requirement is to add what ever Desc's are there for ID '1' to '2' & '3' to '4', below is the expected output.
ID | Desc | Eq |
1 | ABC | 123 |
1 | DEF | 123 |
1 | GHI | 123 |
1 | JKL | 123 |
1 | MNO | 123 |
1 | PQR | 123 |
2 | ABC | 123 |
2 | DEF | 123 |
2 | GHI | 123 |
2 | JKL | 123 |
2 | MNO | 123 |
2 | PQR | 123 |
3 | APP | 343 |
3 | ASS | 343 |
3 | OPP | 343 |
4 | APP | 343 |
4 | ASS | 343 |
4 | OPP | 343 |
Common value between those two rows are Eq.
I'm looking for a load script to achieve it. This is an example data, we have more records in real-time.
Regards,
V
inp_table:
load * inline [
ID,Desc,Eq
1,ABC,123
1,DEF,123
1,GHI,123
1,JKL,123
1,MNO,123
1,PQR,123
2,,123
3,APP,343
3,ASS,343
3,OPP,343
4,,343
];
// Create new entries based on Eq
NewRows:
NoConcatenate
load ID,Eq
Resident inp_table
where len(Desc) = 0;
left join
load Desc,Eq
Resident inp_table
where len(Desc) > 0;
// Combine the rows we need
FinTable:
NoConcatenate
load * Resident inp_table
where len(Desc)>0;
Concatenate
load * Resident NewRows;
drop Table inp_table, NewRows;
exit Script;