Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Can you share a sample loaded file?
My mistake, my code is ok in fact. the problem comes from data.
sorry and thank you for your help
Alright, cheers!
Close the topic though.