Joining two tables

[ajsjoshua]

Dear all,

I am finding difficulties while joining this two tables

Payment:

Load

Company&'_'&if(left(CardCode,1)=0,mid(CardCode,2,15),CardCode) as Payment_QVLink_CustomerKey,

U_Export

FROM

(qvd) where Canceled='N';

left join

PaymentCustomer:

LOAD Company&'_'&if(left(CardCode,1)=0,mid(CardCode,2,15),CardCode) as Payment_QVLink_CustomerKey,

U_ATBeatCode,

  U_BeatCode

FROM

(qvd)

where CardType='C';

When i join only from and i am getting data for the field U_ATBeatCode but when i join from and i am not getting data for the field U_ATBeatCode

Regards,

Joshua.

[shiveshsingh]

Hi, can you try this?

Payment:

Load

Company&'_'&if(left(CardCode,1)=0,mid(CardCode,2,15),CardCode) as Payment_QVLink_CustomerKey,

U_Export

FROM

(qvd) where Canceled='N';

left join

PaymentCustomer:

LOAD Company&'_'&if(left(CardCode,1)=0,mid(CardCode,2,15),CardCode) as Payment_QVLink_CustomerKey,

U_ATBeatCode,

  U_BeatCode

FROM

(qvd)

where CardType='C';

[ajsjoshua]

Dear Shivesh,

I tried as u suggested i am not getting data for the field U_ATBeatCode.

Actually the U_ATBeatCode field has data only for the tables OCRD_SL,OCRD_TM.

U_ATBeatCode has no data for the tables OCRD_UA,OCRD_SA,OCRD_LA.

Regards,

Joshua.

[shiveshsingh]

Hi Joshu

if you don't have values in OCRD_UA,OCRD_SA,OCRD_LA. tables, why do you want to load data from these tables.

It's better to direct pull the values from  OCRD_SL,OCRD_TM.

It may be due to Payment_QVLink_CustomerKey . . . Is this value unique for all the tables?

[ajsjoshua]

Dear Shivesh,

In future there will be values for OCRD_UA,OCRD_SA,OCRD_LA.

Some times in the filed U_ATBeatCode same values will be there in one or two tables.

Regards,

Joshua.

[shiveshsingh]

Hi Joshua

Please check the value of Payment_QVLink_CustomerKey key for these tables once. can u share the app?

[ajsjoshua]

Hi Shivesh ,

Instead of left join i tried join now U_ATBeatCode is coming

if(len(U_ATBeatCode)<>0,U_ATBeatCode,'') as U_ATBeatCode,

now when i load

Paymentdetails:

load

*,

if(U_Export='Y',Company&'_'&Upper(U_ATBeatCode),if(U_Export='N',Company&'_'&Upper(U_BeatCode ))) as Payment_QVLink_BeatKey

Resident Payment;

drop table Payment;

Payment_QVLink_BeatKey doesn't comes when i select U_Export='Y in list box.'

[shiveshsingh]

Can u share the app?