Join using a date

[jleberre]

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!

[siddharth_s3]

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]

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]

Can you share a sample loaded file?

[jleberre]

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

sorry and thank you for your help

[siddharth_s3]

Alright, cheers!

Close the topic though.