topic Re: Complex Join in Load Script in QlikView

Complex Join in Load Script

Anonymous — Sat, 16 Aug 2014 11:51:01 GMT

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

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

Re: Complex Join in Load Script

ecolomer — Sat, 16 Aug 2014 11:58:04 GMT

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

Re: Complex Join in Load Script

sundarakumar — Sat, 16 Aug 2014 12:23:38 GMT

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

Re: Complex Join in Load Script

Anonymous — Sun, 17 Aug 2014 18:27:17 GMT

Yes, just like that

Re: Complex Join in Load Script

Anonymous — Sun, 17 Aug 2014 18:29:47 GMT

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

Re: Complex Join in Load Script

Anonymous — Sun, 17 Aug 2014 18:48:25 GMT

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

];

Re: Complex Join in Load Script

ecolomer — Sun, 17 Aug 2014 18:56:40 GMT

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,

Re: Complex Join in Load Script

Anonymous — Sun, 17 Aug 2014 19:05:12 GMT

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!

Re: Re: Complex Join in Load Script

ecolomer — Sun, 17 Aug 2014 19:53:44 GMT

Mira a ver si así te sirve

Saludos,

Re: Complex Join in Load Script

MarcoWedel — Sun, 17 Aug 2014 22:37:56 GMT

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

Re: Complex Join in Load Script

MarcoWedel — Sat, 20 Sep 2014 07:01:29 GMT

Please mark as answered if there are no further questions.

Thanks

Regards

Marco