Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleberre
Contributor II
Contributor II

Join using a date

Hello

I'm trying to join 2 tables using a date converted in month:

Load

    MonthStart(date(cutomer_date)) as month,

    customer_id,

     ...

Left join

Load

     MonthStart(date(date#(call_date,'YYYYMM'))) as month,

     customer_id,

     ...

Format:

customer date = DD/MM/YYYY

call_date= YYYYMM

MonthStart() function gives the expected result and the "month" field value looks like 01/MM/YYYY, but the Join doesn't work.

Any idea?

Thanks!

5 Replies
siddharth_s3
Partner - Creator II
Partner - Creator II

You are (Or in fact Qlik is) creating a composite key when you are attempting that join.

If you want to join it only by date, try this:

Load

    MonthStart(date(cutomer_date)) as month,

    customer_id,

    ...

Left join

Load

    MonthStart(date(date#(call_date))) as month,

    customer_id as customer_id2

If you want to join it with customer_id & date, try this. This is what your code was doing anyways (almost)

Load

    MonthStart(date(cutomer_date)) as month,

    customer_id,

MonthStart(date(cutomer_date)) & customer_id as key,

...

   

Left join

Load

    MonthStart(date(date#(call_date))) as month2,

    customer_id as customer_id2,

MonthStart(date(date#(call_date))) & customer_id as key

jleberre
Contributor II
Contributor II
Author

OK, forget the field customer_id.

The left join by date only doesn't work.

I don't understand why, because month fields look the same in the 2 tables

siddharth_s3
Partner - Creator II
Partner - Creator II

Can you share a sample loaded file?

jleberre
Contributor II
Contributor II
Author

My mistake, my code is ok in fact. the problem comes from data.

sorry and thank you for your help

siddharth_s3
Partner - Creator II
Partner - Creator II

Alright, cheers!

Close the topic though.