Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to perform a join between these two tables:
T_MASTER:
ID_MASTER | DESC_MASTER |
---|---|
IDM01 | Master Element 1 |
IDM02 | Master Element 2 |
.... | ... |
T_DETAIL
ID_DETAIL | DESC_DETAIL | DETAIL_USED_IN |
---|---|---|
IDD01 | Detail Element 1 | IDM01 IDM05 IDM10 |
IDD02 | Detail Element 2 | IDM02 IDM04 |
To get this final table:
T_USED_IN:
ID_MASTER | ID_DETAIL |
---|---|
IDD01 | IDM01 |
IDD01 | IDM05 |
IDD01 | IDM10 |
IDD02 | IDM02 |
IDD02 | IDM04 |
Any clue will be appreciated.
Regards,
Javier
You need LOAD tbale T_DETAIL first and inner join T_MASTER next
T_DETAIL:
LOAD
ID_DETAIL,
DESC_DETAIL.
DETAIL_USED_IN as ID_MASTER
FROM .....
inner JOIN
LOAD
ID_MASTER,
DESC_MASTER
FROM ....
is the T_DETAIL table pivoted in the source file as u have mentioned in the sample?
Yes, just like that
Thanks for your answer.
Please note that the value of the field DETAIL_USED_IN in T_MASTER is all in one text "IDM01 IDM05 IDM10".
This is kind of join I do not know how to solve.
Regards,
Javi
Something like this maybe ?
T_MASTER:
LOAD * INLINE [
ID_MASTER, DESC_MASTER
IDM01, Master Element 1
IDM02, Master Element 2
];
T_DETAIL:
load
SubField ( DETAILS_USED_IN , ' ' ) as ID_MASTER ,
*
;
LOAD * INLINE [
ID_DETAIL, DESC_DETAIL, DETAILS_USED_IN
IDD01, Details Element 1, IDM01 IDM05 IDM10
IDD02, Details Element 2, IDM02 IDM04
];
Perdona que te escriba en español, ya que por tu nombre debes hablarlo.
Debo estar equivocado o no he entendido nada, pero te adjunto un fichero QV de prueba para trabajar sobre el
Lo puedes revisar porque no se si he interpretado bien y los datos de entrada no están bien.
Saludos,
EC
En la carga de la primera tabla cambialo por esto:
T_DETAIL:
LOAD * INLINE [
ID_DETAIL, DESC_DETAIL, DETAIL_USED_IN
IDD01, Detail Element 1, IDM01 IDM05 IDM10
IDD02, Detail Element 2, IDM02 IDM04
];
Ahi es donde tengo el problema para hacer el JOIN
Gracias!
Mira a ver si así te sirve
Saludos,
EC
Hi Javier,
as your requested T_USED_IN table only contains data from the T_DETAIL table there is no join needed.
The subfield solution proposed by Bill Markham is sufficient:
T_USED_IN:
LOAD ID_DETAIL as ID_MASTER,
SubField(DETAIL_USED_IN, ' ') as ID_DETAIL
Inline [
ID_DETAIL, DETAIL_USED_IN
IDD01, IDM01 IDM05 IDM10
IDD02, IDM02 IDM04
];
hope this helps also
regards
Marco