Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Complex Join in Load Script

Hello,

I need to perform a join between these two tables:

T_MASTER:

ID_MASTERDESC_MASTER
IDM01Master Element 1
IDM02Master Element 2
.......

T_DETAIL

ID_DETAILDESC_DETAILDETAIL_USED_IN
IDD01Detail Element 1

IDM01

IDM05

IDM10

IDD02Detail Element 2

IDM02

IDM04

To get this final table:

T_USED_IN:

ID_MASTERID_DETAIL
IDD01IDM01
IDD01IDM05
IDD01IDM10
IDD02IDM02
IDD02IDM04

Any clue will be appreciated.

Regards,

Javier

10 Replies
ecolomer
Master II
Master II

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
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

Yes, just like that

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Master II
Master II

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
Not applicable
Author

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
Master II
Master II

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

];

QlikCommunity_Thread_129700_Pic1.JPG.jpg

hope this helps also

regards

Marco