LEFT JOIN wont work for qvds

[matthewp]

I have two QVD's that i want to left join on two fields

The code is as follows and i assume this is INNER JOINING both of them.

TABC:

LOAD BNumber AS [TAB B Number],

          [I Number] AS [TAB I Number]

FROM

(qvd);

TABI:

LOAD [TABI.B Reference] AS [TAB B Number],

          [TABI.TABC I Reference] AS [TAB I Number]

FROM

(qvd);

I have tried the following to LEFT JOIN them but it doesn't work and also creates a synthetic key.

QUALIFY *;

UNQUALIFY BNumber, [I Number];

TABC:

LOAD BNumber AS [TAB B Number],

     [I Number] AS [TAB I Number]

FROM

(qvd);

QUALIFY *;

UNQUALIFY [TABI.B Reference], [TABI.TABC I Reference];

LEFT JOIN (TABI)

TABI:

LOAD [TABI.B Reference] AS [TAB B Number],

     [TABI.TABC I Reference] AS [TAB I Number]

FROM

(qvd);

[Clever_Anjos]

So, my script should return what you want, if possible upload some data and evidence that is not working

[oknotsen]

Even if he is saying that, it is not true.

You can join tables with keys of multiple fields.

In other words, this will work fine:

LeftTable:
LOAD * INLINE [
aKey, KeyTwo, ValueOne
1, 1, A
1, 2, B
2, 3, C
2, 4, D
2, 5, E
];

outer join(LeftTable)
RightTable:
LOAD * INLINE [
aKey, KeyTwo, ValueTwo
2, 3, U
2, 4, W
2, 5, X
3, 6, Y
3, 7, Z
];

[tamilarasu]

Yes. You can have two key fields. But this will create synthetic keys. Isnt it?.

[oknotsen]

It isn't.

Only if your final data model has tables that have more then 1 field in common you get synthetic keys. During the run of your script this is no problem at all.

I suggest you try the code I just posted .

[Gabriel]

Hi,

Post a sample data (with qvw) file. It will help quite well.

Some of the suggestions above are correct with LEFT JOIN or OUTER JOIN

[Anonymous]

hi matthew,

Please try:

TABC:

LOAD BNumber AS [TAB B Number],

     [I Number] AS [TAB I Number]

FROM

(qvd);

LEFT JOIN (TABC)

LOAD [TABI.B Reference] AS [TAB B Number],

     [TABI.TABC I Reference] AS [TAB I Number]

FROM

(qvd);

Regards

Neetha

[santiago_respane]

I agree with Manish your join must be with de firt table created.

Also i dont understand the kind of join you are trying to make, you shoud:

• Generaly join adds an extra field to the first table, you have same field names in both tables

Please share some example data for more help.

Regards,

[settu_periasamy]

Hi,

I think, it may be a UNQUALIFY Problem. (Try to give the Alias name in the Unqualify statement) Like..

QUALIFY *;
UNQUALIFY [TAB B Number], [TAB I Number];
TABC:
  LOAD BNumber AS [TAB B Number],
      [I Number] AS [TAB I Number]
  FROM

  (qvd);


QUALIFY *;
UNQUALIFY [TAB B Number], [TAB I Number];


LEFT JOIN (TABC)


TABI:
  LOAD [TABI.B Reference] AS [TAB B Number],
      [TABI.TABC I Reference] AS [TAB I Number]
  FROM

  (qvd);

see the example script

QUALIFY *;
UNQUALIFY [TAB B Number], [TAB I Number];
TABC:
LOAD BNumber AS [TAB B Number],
     [I Number] AS [TAB I Number];
LOAD * INLINE [
    BNumber, I Number
    a, 25
    b, 85
    c, 45
    d, 15
];


QUALIFY *;
UNQUALIFY [TAB B Number], [TAB I Number];
LEFT JOIN (TABC)
TABI:
LOAD [TABI.B Reference] AS [TAB B Number],
     [TABI.TABC I Reference] AS [TAB I Number],
     Data;     
LOAD * INLINE [
    TABI.B Reference, TABI.TABC I Reference, Data
    a, 25, 1
    b, 85, 2
    c, 45, 3
    d, 15, 4
];