Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Script that you shared is
Table2:
Load
[Rate],
date(MonthStart(date#(MonthYear,'MMM YYYY')),'MMM YYYY') as MonthYear,
Value
Resident date_2;
Drop tables Table2;
It should be
noconcatenate
Table2:
Load
[Rate],
date(MonthStart(date#(MonthYear,'MMM YYYY')),'MMM YYYY') as MonthYear,
Value
Resident date_2;
Drop tables date_2;
"Drop table Table2" should be "Drop table date_2" and add noconcatenate before table2 script to prevent autoconcatanation to date_2 table.
Try to load distinct at date_1 table.
I tried Distinct and it did not work as well.
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;
I did and it appeared to be same still. No luck!
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;
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;
It worked finally !
Thanks a lot!
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;
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...