Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

qlikdash
Contributor II

How to Join/Link different Dates

Hi folks,

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

Table 1

Field ADate PurchasedField B
11/1/2017a
21/2/2017b
32/1/2017c
42/28/2017d
53/1/2017e
64/1/2017f
74/2/2017g

Table 2

Field CDate orderedField D
1001/4/2017p
2001/13/2017q
3002/20/2017r
4002/25/2017s
5003/1/2017t
6004/5/2017u
7004/6/2017v

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-MonthStartField AField C
Jan 2017
Feb 20177300
Mar 20175700
Apr 201713500

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

1 Solution

Accepted Solutions

Re: How to Join/Link different Dates

Hi,

welcome back.


One solution might be:

QlikCommunity_Thread_280447_Pic1.JPG

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

4 Replies
qlikdash
Contributor II

Re: How to Join/Link different Dates

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,

Re: How to Join/Link different Dates

Hi,

welcome back.


One solution might be:

QlikCommunity_Thread_280447_Pic1.JPG

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

qlikdash
Contributor II

Re: How to Join/Link different Dates

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

Regards.

qlikdash
Contributor II

Re: How to Join/Link different Dates

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 ADate PurchasedCountryRegionTaxable
11/1/2017USAAY
21/2/2017USABN
31/3/2017USACN
42/1/2017INDAY
52/2/2017RUSBN
62/3/2017CHIBY
72/4/2017USACY

Table 2:


Field CDate OrderedCountryRegionTaxable
10012/1/2016USAAY
20012/2/2016USABN
30012/3/2016USACN
4001/1/2017INDAY
5001/2/2017RUSBN
6001/3/2017CHIBY
7001/4/2017BRACY

Result:

Date Purchased-MonthstartField AField C
Jan 20176600
Feb 2017221200

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.