Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Do a comparison on 2 fields in a table

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

4 Replies
Specialist III
Specialist III

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.

Partner
Partner

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;

Muthukumar Pandiyan
Not applicable

  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],
g
iftplgkey 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;

Specialist III
Specialist III

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],
g
iftplgkey 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;