topic Data Model, Table Join help in QlikView

Data Model, Table Join help

pduplessis — Mon, 06 Sep 2010 21:57:43 GMT

Dear Forum members.

After much searching here and trying many ideas, I have decided to post my question here, in the hopes somebody can help.

I have two tables, A & B say, which have to join on a field which is not a key field in either table. The entries in Table A for this field are in a one to many relationship with entries in table B. The business problem requires that I find for each unique entry in table A, a corresponding entry in Table B, which is possible when the other fields in table are considered.

More specifically: Table A contains Claims and table B contains features of the policy which are date dependant: Only 1 feature is valid per claim.

I thought of using interval match, but haven't been successful.

My next thought is, and where I need help with the syntax is :

Load table A, then inner join with table B, with a WHERE clause that depends on the values of table A.

here is a simplified example:

TableA:

LOAD * INLINE [

ClaimNo, Discount, ClaimDate

100000, A, 1/9/2010

100001, B, 1/9/2010

100002, B, 2/9/2010

100003, B, 3/9/2010

100004, A, 2/9/2010

100005, B, 2/9/2010

100006, A, 3/9/2010

100007, B, 1/9/2010

100008, A, 2/9/2010

100009, B, 3/9/2010

];

TableB:

LOAD * INLINE [

DiscountFeature, Discount, Start, End

100, A, 1/9/2010, 2/9/2010

100, B, 3/9/2010, 6/9/2010

101, C, 2/9/2010, 3/9/2010

101, F, 1/9/2010, 1/9/2010

102, D, 1/9/2010, 3/9/2010

103, A, 3/9/2010, 6/9/2010

103, B, 1/9/2010, 2/9/2010

104, E, 1/9/2010, 6/9/2010

105, C, 1/9/2010, 1/9/2010

105, G, 2/9/2010, 3/9/2010

106, F, 2/9/2010, 3/9/2010

106, G, 1/9/2010, 1/9/2010

];

In the end, I would like a table in the data model, with ClaimNo and DiscountFeature. Right now the resulting table with the inner join contains 2 records for each claim, one in the valid period and one not. I can do most expressions by using the claim date to filter right answer, but I think that is too expensive. I also don't need twice the records I get from the join.

So, obviously this is simplified, I would be happy to answer any questions for ideas to try.

Data Model, Table Join help

Tue, 07 Sep 2010 01:49:37 GMT

Hi,

Would it be true to say that the ClaimDate will always be equal to the Start Date?

Data Model, Table Join help

disqr_rm — Tue, 07 Sep 2010 02:19:34 GMT

Looks like you just need an Intervalmatch. Try following after your tale inline loads:



IntervalMatch(ClaimDate, Discount)
LOAD Start, End, Discount RESIDENT TableB;

And you can surely left join all this back to main table, if you are interested.

Data Model, Table Join help

Tue, 07 Sep 2010 03:30:50 GMT

Hi Rakesh,

That just seems to be returning the same results.

Data Model, Table Join help

Tue, 07 Sep 2010 08:42:10 GMT

Hi Xena,

You find sample this code.

TABLEA:

LOAD

INLINE

[

;

TABLEB:

OUTER

JOIN

LOAD

INLINE

[

;

TABLEC:

NOCONCATENATE

LOAD

ClaimNo

ClaimDate

Discount

DiscountFeature

Start

End

RESIDENT

TABLEA

WHERE

ClaimDate >= Start AND ClaimDate <=

End;

DROP

TABLE

TABLEA;

Hope, it will help you.

Regards,

Raj Kishor

Data Model, Table Join help

Tue, 07 Sep 2010 08:44:19 GMT

Hi,

Please find the sample code to achieve this.

TABLEA:
LOAD * INLINE
[
ClaimNo, Discount, ClaimDate
100000, A, 1/9/2010
100001, B, 1/9/2010
100002, B, 2/9/2010
100003, B, 3/9/2010
100004, A, 2/9/2010
100005, B, 2/9/2010
100006, A, 3/9/2010
100007, B, 1/9/2010
100008, A, 2/9/2010
100009, B, 3/9/2010
];

TABLEB:
OUTER JOIN
LOAD * INLINE
[
DiscountFeature, Discount, Start, End
100, A, 1/9/2010, 2/9/2010
100, B, 3/9/2010, 6/9/2010
101, C, 2/9/2010, 3/9/2010
101, F, 1/9/2010, 1/9/2010
102, D, 1/9/2010, 3/9/2010
103, A, 3/9/2010, 6/9/2010
103, B, 1/9/2010, 2/9/2010
104, E, 1/9/2010, 6/9/2010
105, C, 1/9/2010, 1/9/2010
105, G, 2/9/2010, 3/9/2010
106, F, 2/9/2010, 3/9/2010
106, G, 1/9/2010, 1/9/2010
];

TABLEC:
NOCONCATENATE
LOAD ClaimNo,
ClaimDate,
Discount,
DiscountFeature,
Start,
End
RESIDENT TABLEA
WHERE ClaimDate >= Start AND ClaimDate <= End;

DROP TABLE TABLEA;

Hope, this will help you.

Regards,

Raj Kishor

Data Model, Table Join help

Tue, 07 Sep 2010 08:48:54 GMT

Hi,

Please find the sample code.

TABLEC:
NOCONCATENATE
LOAD ClaimNo,
ClaimDate,
Discount,
DiscountFeature,
Start,
End
RESIDENT TABLEA
WHERE ClaimDate >= Start AND ClaimDate <= End;

DROP TABLE TABLEA;

Hope, it will help you.

Regards,

Raj Kishor

Data Model, Table Join help

pduplessis — Tue, 07 Sep 2010 10:07:03 GMT

Thanks Raj,