Join 2 Tables using certain conditions

[#1]

Hello All,

I have 2 tables that I ultimately want to 'join' when certain criteria are met.

Table1:

AcctNum,Date,Amount

212,2/2/2012,$50

212,2/5/2012,0

Table2:

AcctNum,Date,Code,

212,2/2/2012,A25

Ultimately, I want to be able to have Table one display the following way (only add code/join when the following is satisfied, when account matches in both tables, the dates are the same and when the amount in Table 1 is not '0' ) but I am not able to do this using a left join:

Table1:

AcctNum,Date,Amount,Code

212,2/2/2012,$50,A25

Issue is that i get the following, a code is inserted regarless:

AcctNum,Date,Amount,Code

212,2/2/2012,$50,A25

212,2/5/2012,0,A25

Please help me

[ericcosmai]

Hi.

Follow what I got:

Table1:
LOAD * Inline [
AcctNum,Date,Amount
212,2/2/2012,$50
212,2/5/2012,0
];

Table2:
LOAD * Inline [
AcctNum,Date,Code
212,2/2/2012,A25
];

Left Join(Table2)
LOAD
AcctNum,
Date,
Amount
Resident Table1
Where Amount<>0;

RENAME Table Table2 to Table;

DROP Table Table1;

Hope it helps.

Regards.

[#3]

Eric,

Thanks for the response. can i achieve this by joining to Table1 instead of to Table2. I ask this because there are other fields/Rows that I use in Table1 that I think would be lost if i did the join to Table1.

For instance, If Table1 has 1000 rows of data and Table2 has only 100 rows of data that could possibly join, what happens to the other 900 rows of data in Table1.

Thanks

[ericcosmai]

Hey man...

Attached the app... take a look....

I hope it helps.

Regards.

[ericcosmai]

Hi...

It helps?