Complex Join in Load Script

[Anonymous]

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

[ecolomer]

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 ....

[sundarakumar]

is the T_DETAIL table pivoted in the source file as u have mentioned in the sample?

[Anonymous]

Yes, just like that

[Anonymous]

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

[Anonymous]

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

];

[ecolomer]

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

[Anonymous]

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!

[ecolomer]

Mira a ver si así te sirve

Saludos,

EC

[MarcoWedel]

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