Adding columns from one table to another based on nearest date

I have a table with product test information and another with product specs.

Subset Unit Test Results

Unit

Product

VarA

VarB

Test Date

123

A

1.1

33

1/1/2017

234

C

1.2

35

2/1/2017

432

B

1.1

32

7/1/2018

456

A

1.5

35

11/7/2018

 

Subset Product Specs

Product

VarA_Min

VarA_Max

SpecDate

A

1

1.5

12/30/2016

B

1

2

12/30/2016

C

0.9

1.3

12/30/2016

A

0.8

1.3

3/1/2018

B

0.9

1.4

6/30/2018

Want to combine the information by adding columns from the Product Specs table to the Test Data Results by selecting the spec with the SpecDate that would have been current at the time of the product test.

Combined Table

Unit

Product

VarA

VarB

Test Date

VarA_Min

VarA_Max

123

A

1.1

33

1/1/2017

1

1.5

234

C

1.2

35

2/1/2017

0.9

1.3

432

B

1.1

32

7/1/2018

0.9

1.4

456

A

1.5

35

11/7/2018

0.8

1.3

Ideally I'd want to do this using Information Designer (both are SQL tables) but could use some help even if it's within standard calculated column additions within Spotfire.

 

(1) Answer

Login