Do a comparison on 2 fields in a table

[#1]

Table1:   Giftplgkey, giftcampdt

Table 2: Pledgekey, pledgecampdt

I do a join where giftplgkey = pledgekey

But I only want to display the rows where giftpcampdt > pledgecampdt

[sinanozdemir]

Hi,

After joining the tables, do a resident load and put the condition in the where statement:

[Final Table]:

LOAD

     *

Resident [The First Table]

Where giftpcampdt > pledgecampdt;

Drop Table [The First Table];

Hope this helps.

[muthukumar77]

Hi,

Table:

Load Giftplgkey,giftcampdt from Table1;

join

Load Pledgekey as Giftplgkey, pledgecampdt from Table 2;

Final:

Load * where flag=1;

Load *, If(giftpcampdt > pledgecampdt,1,0) as flag

resident Table;

[#4]

  Here is my actual code paired down some.  When I try to do what you said It doesn't recognize the table Gifts?

Gifts:
LOAD
giftkey AS [Gift Key],
Text(giftid) AS [Millennium ID],
giftcampyr AS [Gift Campaign Year],
giftplgkey AS [Gift Pledge Key],

SQL SELECT
giftkey AS "giftkey",
giftid AS "giftid",
giftcampyr AS "giftcampyr",
giftplgkey AS "giftplgkey", 

FROM
$(vGiftsView);

 
Pledges:
LOAD
giftkey AS [Pledges Key],
Text(giftid) AS [Millennium ID],
Text(giftid) AS [Pledges ID],


     giftcampyr AS [Pledges Campaign Year],

     giftplgkey AS [Pledges Pledge Key];
    
SQL SELECT
giftkey AS "giftkey",
giftid AS "giftid",
giftplgkey AS "giftplgkey"


FROM
$(vGiftsView); 
LEFT JOIN (Gifts) LOAD
[Pledges Key] AS [Gift Pledge Key],
[Pledges Key] AS [Gift Original Pledge Key],
[Pledges Campaign Year] AS [Gift Pledge Campaign Year]

RESIDENT      Pledges;

[sinanozdemir]

I think you should join Pledges to Gifts table. At least it seems like that's what you are trying to do:

Gifts:
LOAD
giftkey AS [Gift Key],
Text(giftid) AS [Millennium ID],
giftcampyr AS [Gift Campaign Year],
giftplgkey AS [Gift Pledge Key],
SQL SELECT
giftkey AS "giftkey",
giftid AS "giftid",
giftcampyr AS "giftcampyr",
giftplgkey AS "giftplgkey", 
FROM
$(vGiftsView);

Left Join(Gifts) 
Pledges:
LOAD
giftkey AS [Pledges Key],
Text(giftid) AS [Millennium ID],
Text(giftid) AS [Pledges ID],
giftcampyr AS [Pledges Campaign Year],
giftplgkey AS [Pledges Pledge Key];
SQL SELECT
giftkey AS "giftkey",
giftid AS "giftid",
giftplgkey AS "giftplgkey"
FROM
$(vGiftsView); 

NoConcetenate

[Final Table]:

LOAD

    *

Resident Gifts

Where [Gift Campaign Year] > [Pledges Campaign Year];

Drop Table Gifts;