Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

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,

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

View solution in original post

Highlighted
Specialist
Specialist

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

Regards.

Highlighted
Specialist
Specialist

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.

Highlighted
Contributor
Contributor

Hi 

did you find an answer to your problem 

I'm dealing with the same issue