Mapping date column with Cross Table

[LP27]

Hi Everyone,

I am trying to mapping date fields in two tables, One table i am using cross tables for date field.

It produces duplicates after mapping the date field, not sure how to fix the issue.

Attached the Qlik sense app below for reference.

Thanks in advance,

[Arthur_Fong]

Try to load distinct at date_1 table.

[LP27]

I tried Distinct and it did not work as well. 

[Arthur_Fong]

Try to replace "Table2" script with this:

Table2:
Load

[Rate],
date(MonthStart(date#(MonthYear,'MMM YYYY')),'MMM YYYY') ,
Value
Resident date_2;
Drop tables Table2;

[LP27]

I did and it appeared to be same still. No luck!

[Arthur_Fong]

Try to replace 'Table2' with this:

Table2:
Load

[Rate],
date(MonthStart(MonthYear),'MMM YYYY') ,
Value
Resident date_2;
Drop table date_2;
Rename table Table2 to date_2;

[Arthur_Fong]

Try this if problem still not solved:

Table2:
Load

[Rate],
date(MonthStart(date#(MonthYear),'MMM YYYY'),'MMM YYYY') ,
Value
Resident date_2;
Drop table date_2;
Rename table Table2 to date_2;

[LP27]

It worked finally !

Thanks a lot!

[Arthur_Fong]

You might also need to rename your transformed MonthYear column to map with table1:

Table2:
Load

[Rate],
date(MonthStart(date#(MonthYear),'MMM YYYY'),'MMM YYYY') as MonthYear ,
Value
Resident date_2;
Drop table date_2;
Rename table Table2 to date_2;

[kaanerisen]

Hi LP27,

You should change the table_2 script as below.

Table2:
Load 
     [Rate],
     date(MonthStart(date#(MonthYear,'MMM YYYY')),'MMM YYYY') as MonthYear,
     Value
    Resident date_2;

MonthYear data on table_2 doesn't recognize as a date field by qlik because qlik doesn't know how the data string should be evaluated as a date. By using Date# function,  you should describe the format of the text string to be evaluated.

Hope it helps...