Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
wendytham
Contributor II
Contributor II

How to merge data against the latest date?

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 IdItem NumberFrom dateTo dateAmount currency
764000-020170C10-07-13 471,813.00
1093000CCP09203/15/2012 1,477.30
2393000FLW0141AM11/19/2018 39,816.00
1164600HTN0101D12/14/2011 3,918.20
668300MSM0403GS9/24/2010 3,274.70
1422300PAG1012-M05-01-1311/18/201644,459.00
1422300PAG1012-M11/19/2016 57,681.00
1422300PAG1012-M11/19/2016 57,681.00

 

item_sample:

STO dateItem numberQty
3/15/2012AVE29-1582
11/19/2018AVE29-1581
12/14/2011FFE41801801S3
9/24/2010ACXH02C010701
05-01-13E2411631
11/19/2016W9001-0MT011
11/19/201691-1179001
05-02-19VYK6F051
3/30/2009FFE41801801S1
04-01-1123000306941
06-05-08L0903-0H20NKAR10
03-01-11ZCP200JXDG10
04-01-11ZCP129JXDI20
05-07-15AD-384231-S1
11/19/2016CWM-110020120097641
11-05-15E24116310
Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

3 Replies
Taoufiq_Zarra
Master II
Master II

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

wendytham
Contributor II
Contributor II
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.