How to get Closest Date

[#1]

Hi,

I have Table(Table1) and need to get the column "State" from Table 2.I want the state with the same Number and the closest Date2 from Date 1 but can't get it right.

Here's an example:

Table1:

Number     Date1

1               16/08/2016   

1               21/08/2016

1               26/08/2016

2               07/08/2016

2               16/08/2016

3               07/08/2016

3               12/08/2016

Table2:

Number     Date2             State

1              10/08/2016             A

1              14/08/2016             B

1              20/08/2016             D

1              28/08/2016             B

2              05/08/2016             C

2              10/08/2016             C

2              20/08/2016             B

3              04/08/2016             A

3              06/08/2016             A

3              14/08/2016             B

3              28/08/2016             C

Final Table:

Number     Date1           State

1               16/08/2016          B

1               21/08/2016          D

1               26/08/2016          B

2               07/08/2016          C

2               16/08/2016          B

3               07/08/2016          A

3               12/08/2016          B

Regards,

Francisco.

[sunny_talwar]

Hi Francisco -

Do you literally mean the closest date? It could be above or below the dates you have?

[afurtado]

Hi Francisco.

What you mean with "closest Date"  To Up or Down?

For this

Table1:

Number     Date1

1               16/08/2016

you have in table2

Table2:

Number     Date2             State

1              10/08/2016             A

1              14/08/2016             B

1              20/08/2016             D

1              28/08/2016             B

and the final was

Final Table:

Number     Date1           State

1               16/08/2016          B

To Number 1 and Date1 16/08/2016, the closest (before 16/08/2016) was 14/08/2016 and State B (like you TableFinal)

But, in other hand....

Table1:

Number     Date1

1               26/08/2016

Table2:

Number     Date2             State

1              10/08/2016             A

1              14/08/2016             B

1              20/08/2016             D

1              28/08/2016             B

Final Table:

Number     Date1           State

1               26/08/2016          B

Would be B  or D (20/08/2016 - D)?

I did like this

Numero	DataInicio1	DataInicio2	Estado
1	16/08/2016	14/08/2016	B
1	21/08/2016	20/08/2016	D
1	26/08/2016	20/08/2016	D
2	07/08/2016	05/08/2016	C
2	16/08/2016	10/08/2016	C
3	07/08/2016	06/08/2016	A
3	12/08/2016	06/08/2016	A

[#4]

It was literally the closest date, Antonio Mancini got it right in the other topic. How to get Closest Date‌

[#5]

It was the closest date, independent if it was up or down, in the case you mentioned the state would be B.

[sunny_talwar]

Mark this discussion as Assumed Answered.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny