Compare & flag values in same table based on value in another table

ciaran_mcgowan — Mon, 24 Mar 2014 12:12:16 GMT

Hi all,

I have a database containing tables called ACCOUNT, CONTRACT & CONTRACTDETAILS. I have two tables in QV where one flags if contracts are going to expire within the next 60 days and another that shows all the new contracts currently in the process of being processed.

What I would like to do is to place a flag in the "Contract < 60 days" table if there is a similarly named contract currently being renewed. All contracts (current & upcoming) are stored in the CONTRACTS table in the database and the flag for upcoming projects (Status = 'Planned') is stored in the CONTRACTDETAILS table - all other info such as Start/End Dates, etc. are stored in the CONTRACT table. In QV, the tables look similar to below:

Contract < 60 Days:

ACCOUNT	CONTRACT REF	ENDDATE
Duffy's Shop	DUF - Support 2013-2014	01/04/2014
Sharp Supermarket	SHP - Maintenance 2013-2014	07/04/2014

Upcoming Contracts:

ACCOUNT	CONTRACT REF	STATUS	STARTDATE
Duffy's Shop	DUF - Support 2014-2015	Planned	02/04/2014

What I'm looking to accomplish is to read all values in the CONTRACTS table and flag if there is a similar named CONTRACT REF for the same account, where the Start Date (upcoming) >= End Date (current).

topic Compare & flag values in same table based on value in another table in QlikView

Compare & flag values in same table based on value in another table