topic Re: Formula based on 2 "tables" in QlikView

Formula based on 2 "tables"

cbushey1 — Wed, 03 Dec 2014 14:50:35 GMT

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

,StartDate

,EndDate

From

Source1

Table2:

Load

,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?

Re: Formula based on 2 "tables"

Anonymous — Wed, 03 Dec 2014 14:57:01 GMT

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

,StartDate

,EndDate

From

Source1

left join

Load Distinct

,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

Re: Formula based on 2 "tables"

JonnyPoole — Wed, 03 Dec 2014 14:57:36 GMT

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

Re: Formula based on 2 "tables"

yduval75 — Wed, 03 Dec 2014 14:58:45 GMT

Hello Chase,

Table1:

Load

,StartDate

,EndDate

From

Source1

Left join Table 1

Load

, 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;

Re: Formula based on 2 "tables"

cbushey1 — Wed, 03 Dec 2014 15:06:04 GMT

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..

Re: Formula based on 2 "tables"

yduval75 — Wed, 03 Dec 2014 15:09:49 GMT

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..

Re: Formula based on 2 "tables"

cbushey1 — Wed, 03 Dec 2014 15:12:55 GMT

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.

Re: Formula based on 2 "tables"

stigchel — Wed, 03 Dec 2014 15:13:55 GMT

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')

Re: Formula based on 2 "tables"

cbushey1 — Wed, 03 Dec 2014 18:26:21 GMT

Thanks everyone!

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

Chase