topic Re: Join using a date in App Development

Join using a date

jleberre — Thu, 18 Jan 2018 08:44:25 GMT

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!

Re: Join using a date

siddharth_s3 — Thu, 18 Jan 2018 09:00:54 GMT

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

Re: Join using a date

jleberre — Thu, 18 Jan 2018 15:55:28 GMT

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

Re: Join using a date

siddharth_s3 — Thu, 18 Jan 2018 16:00:07 GMT

Can you share a sample loaded file?

Re: Join using a date

jleberre — Fri, 19 Jan 2018 13:11:35 GMT

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

sorry and thank you for your help

Re: Join using a date

siddharth_s3 — Fri, 19 Jan 2018 13:33:39 GMT

Alright, cheers!

Close the topic though.