Formula based on 2 "tables"

[cbushey1]

I know how this works in SQL, but not sure how to translate this for QV code.

I want to create a formula that is based on fields in two separate tables or load statements.

I want a formula that is something like this:

     If (ClaimDate >= StartDate and ClaimDate <= EndDate, 1, 0) as DeleteFlag

Table1:

Load

     ID

     ,StartDate

     ,EndDate

From

Source1

Table2:

Load

     ID

     ,ClaimDate

From

     Source2

Joining the tables is not really an option because in Table2 the ID is listed multiple times (claim lines).

Thoughts for linking the two tables together in order to create this flag?

[Anonymous]

Hi try Taking Distinct assuming ID column is linking field or Key in both the tables and use left or right or inner join  and then link the tables and then apply the If condition

Table1:

Load

     ID

     ,StartDate

     ,EndDate

From

Source1

left join

Load Distinct

     ID

     ,ClaimDate

From

     Source2

Table_final:

load

*, If (ClaimDate >= StartDate and ClaimDate <= EndDate, 1, 0) as DeleteFlag

resident

Table1;

drop table Table1;

you need to convert the date as well if required

Regards

Harsha

[JonnyPoole]

The first table sounds like a Policy table and the 2nd a Claim table where you might have 0 to multiple claim IDs for a Policy ID. Is that true ? 

In this case you would make sure the Claim table has both the policy ID and Claim ID and that the field ID is consistent between table 1 and 2 so as to join/associate on PolicyID.

I don't see an issue with this method.

Intervalmatch() is another way to do it in the data model

[yduval75]

Hello Chase,

Table1:

Load

     ID

     ,StartDate

     ,EndDate

From

Source1

Left join Table 1

Load

     ID

     , Max (ClaimDate) as ClaimDate

From

     Source2

Group By ID;

Tablenew:

LOAD

  *,

  If (ClaimDate >= StartDate and ClaimDate <= EndDate, 1, 0) as DeleteFlag

Resident Table1;

Drop Table Table1;

[cbushey1]

The problem is I dont want to join my tables into one because it causes duplicate records due to the nature of the data in Table 2.

The data in table 1, would have 1 line per ID containing a set of dates. The data in table 2 could have multiple lines of the same ID and different dates.

Table 1:

Id = 1

StartDate = 12/1/14

EndDate = 12/2/14

Table2:

ID = 1

ClaimDate = 11/30/14

ID = 1

ClaimDate = 12/1/14

ID = 1

ClaimDate = 12/2/14

ID = 2

ClaimDate = 11/25/14
etc..

[yduval75]

If you don't want duplicate recodes due to multiple values in Table 2, you have to aggregate the table 2 by ID with Max, Min, etc..

[cbushey1]

The problem is I cant aggregate the data just yet.

Table 2 has dates and amounts and I need to identify which lines I need to keep and which I can delete.

I was trying to flag those that I can delete by my DeleteFlag statement.

[stigchel · Accepted solution]

Maybe you can use a lookup ?

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

[cbushey1]

Thanks everyone!

The lookup should handle what I am trying to do best.

Chase