topic Re: How to get Closest Date in QlikView

How to get Closest Date

Fri, 23 Sep 2016 13:51:32 GMT

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.

Re: How to get Closest Date

sunny_talwar — Fri, 23 Sep 2016 15:42:35 GMT

Hi Francisco -

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

Re: How to get Closest Date

afurtado — Fri, 23 Sep 2016 21:41:21 GMT

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

Re: How to get Closest Date

Mon, 26 Sep 2016 12:37:18 GMT

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

Re: How to get Closest Date

Mon, 26 Sep 2016 12:38:24 GMT

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

Re: How to get Closest Date

sunny_talwar — Mon, 26 Sep 2016 17:04:24 GMT

Mark this discussion as Assumed Answered.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny