Left Join table field to First table

[settu_periasamy]

Hi,

i have 2 qvd file and i need to join them like the below code.

Table1:

  LOAD     A1,    

               B1,

               if(not isnull(D1),0,A1) as C1   // D1 Field is in 2nd qvd    

  FROM

  qvd\1.qvd

  (qvd) where exists(F1);

  Left Join 

LOAD   D1,  E1   FROM   qvd\2.qvd   (qvd) where exists(E1);

store table1 to table1.qvd;

I'm getting an error field not found. I know D1 field not available in the 1st qvd file.

But i need the C1 field, if D1 is not null.

Could you please give the suggestion?

[jagan]

Hi,

What are the key fields between Qvd1 and QVD2?  Where is the column F1?  can you attach some sample data and required output.

Regards,

Jagan.

[settu_periasamy]

hi jagan,

QVD1 have A1, B1 and F1.

QVD2 have D1,E1 and F1.

I need the output like all the data from QVD1 and match the data from QVD2.

C1 is the calculated field from the QVD2.

i dont have the correct sample data.

[jagan]

Hi,

On what basis QVD1 and QVD2 are joined?  Is there any key fields?

Regards,

Jagan.

[settu_periasamy]

Yes. F1 is the Key Field.

[vardhancse]

to do any left join, we need to have one common field.

[MayilVahanan]

Hi

Try like this

A:

Load * from Qvd1;

Left join(A)

Load * from Qvd2;

Noconcatenate

Load A1, B1, if(not isnull(D1),0,A1) as C1, F1, D1, E1 Resident A;

Drop table A;

[#8]

Was F1 previously loaded? without that you can use it where exists. Or may be you haven't posted the entire script.

[er_mohit]

table1:

load

A1, B1 ,F1 from QVD1 ;

left join(table1)

table2:

load

D1,E1 ,F1 from QVD2 where exist(F1);

noconcatenate

Final:

load * resident table1;  //here you calculate the field for C1

drop table table1;

[jagan · Accepted solution]

Hi,

Try like this

Table1:

Load

A1, B1 ,F1

from QVD1 ;

Left Join(Table1)

Load

D1,E1 ,F1

from QVD2 where Exists(F1);

Final:

Load *,

If(Not IsNull(D1),0, A1) AS C1

Resident Table1;

Drop Table Table1;

Regards,

Jagan.