Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jleberre
New Contributor

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
Contributor

Re: Join using a date

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
New Contributor

Re: Join using a date

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
Contributor

Re: Join using a date

Can you share a sample loaded file?

jleberre
New Contributor

Re: Join using a date

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

sorry and thank you for your help

siddharth_s3
Contributor

Re: Join using a date

Alright, cheers!

Close the topic though.

Community Browser