How to merge data against the latest date?

[wendytham]

Hi All,

I have 2 files, one is with the price with date validity, the other one is the date and the quantity.

How to merge the data against latest date from the excel file and merge with the STO date in item_sample file?

price_sample:

Those with empty cell "To date" is valid till now.

Item Id	Item Number	From date	To date	Amount currency
7640	00-020170C	10-07-13		471,813.00
10930	00CCP0920	3/15/2012		1,477.30
23930	00FLW0141AM	11/19/2018		39,816.00
11646	00HTN0101D	12/14/2011		3,918.20
6683	00MSM0403GS	9/24/2010		3,274.70
14223	00PAG1012-M	05-01-13	11/18/2016	44,459.00
14223	00PAG1012-M	11/19/2016		57,681.00
14223	00PAG1012-M	11/19/2016		57,681.00

item_sample:

STO date	Item number	Qty
3/15/2012	AVE29-158	2
11/19/2018	AVE29-158	1
12/14/2011	FFE41801801S	3
9/24/2010	ACXH02C01070	1
05-01-13	E241163	1
11/19/2016	W9001-0MT01	1
11/19/2016	91-117900	1
05-02-19	VYK6F05	1
3/30/2009	FFE41801801S	1
04-01-11	2300030694	1
06-05-08	L0903-0H20NKAR	10
03-01-11	ZCP200JXDG	10
04-01-11	ZCP129JXDI	20
05-07-15	AD-384231-S	1
11/19/2016	CWM-11002012009764	1
11-05-15	E241163	10

[Taoufiq_Zarra · Accepted solution]

Hi,

the solution is the use of Qlikview's IntervalMatch function.
Attached is a proposed Qlikview solution.

But,

Is it normal that most of the [Item Number] in one file does not exist in the other?

If you have another sample data file where I can check, please share it

[wendytham]

Hi,

I tried to change the date for one Item Number, it doesn't work well.

If the From date and STO date is same, the amount should be showed, but it didn't.

Can you help on this?

Thanks.

[Brett_Bleess]

Wendy, have a look at the following Design Blog posts, I think they should help regarding the dates...

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

Just in case you want to search this area yourself, here is the URL to do that, there are hundreds of mostly how-to posts in this area from our experts...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett