How to Join/Link different Dates

[Anonymous]

Hi folks,

I have a particular requirement. I want to link 2 tables based on dates.

Table 1

Field A	Date Purchased	Field B
1	1/1/2017	a
2	1/2/2017	b
3	2/1/2017	c
4	2/28/2017	d
5	3/1/2017	e
6	4/1/2017	f
7	4/2/2017	g

Table 2

Field C	Date ordered	Field D
100	1/4/2017	p
200	1/13/2017	q
300	2/20/2017	r
400	2/25/2017	s
500	3/1/2017	t
600	4/5/2017	u
700	4/6/2017	v

how do I link table 2 so that Date ordered is one month behind from Date Purchase. meaning: I want records in Feb (Date Purchased) to link with Jan in (Date ordered). Likewise, when the rows are summed, Result should be:

Date Purchased-MonthStart	Field A	Field C
Jan 2017
Feb 2017	7	300
Mar 2017	5	700
Apr 2017	13	500

It has been a while in the community. I am back. feels good to be back.

[Anonymous]

ignore the Jan data for table 2 because the relevant data would have been december 2016, which is not there. so null value is fine for that particular row.

Thanks,

[MarcoWedel · Accepted solution]

Hi,

welcome back.

One solution might be:

Table1:
LOAD [Field A],
    [Date Purchased],
    MonthName([Date Purchased]) as MonthPurchased,
    [Field B]
FROM [https://community.qlik.com/thread/280447] (html, codepage is 1252, embedded labels, table is @1);

Table2:
LOAD [Field C],
    [Date ordered],
    MonthName([Date ordered],1) as MonthPurchased,
    [Field D]
FROM [https://community.qlik.com/thread/280447] (html, codepage is 1252, embedded labels, table is @2);

hope this helps

regards

Marco

[Anonymous]

Hi Marco Thank you for the quick reply. I will try the soln and post it back here.

Regards.

[Anonymous]

Marco Thanks again for your help.

As always, the requirements in reality are a lil complex 🙂

addtional reqs are Table 1 & 2 has 3 common fields Country, Region and Taxable. so when joining Date purchased with Date Ordered (one month behind)- how to implement your logic while linking these 3 common fields. I have created the new tables below.

Table 1:

Field A	Date Purchased	Country	Region	Taxable
1	1/1/2017	USA	A	Y
2	1/2/2017	USA	B	N
3	1/3/2017	USA	C	N
4	2/1/2017	IND	A	Y
5	2/2/2017	RUS	B	N
6	2/3/2017	CHI	B	Y
7	2/4/2017	USA	C	Y

Table 2:

Field C	Date Ordered	Country	Region	Taxable
100	12/1/2016	USA	A	Y
200	12/2/2016	USA	B	N
300	12/3/2016	USA	C	N
400	1/1/2017	IND	A	Y
500	1/2/2017	RUS	B	N
600	1/3/2017	CHI	B	Y
700	1/4/2017	BRA	C	Y

Result:

Date Purchased-Monthstart	Field A	Field C
Jan 2017	6	600
Feb 2017	22	1200

when summing these 3 new fields also should match. For eg, I included all rows from Table 2 except last row where Country is Brazil.

[arieltopaz]

Hi 

did you find an answer to your problem 

I'm dealing with the same issue