topic Re: Mapping date column with Cross Table in App Development

Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 09:32:33 GMT

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,

Re: Mapping date column with Cross Table

Arthur_Fong — Wed, 28 Aug 2019 00:45:50 GMT

Try to load distinct at date_1 table.

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 05:32:28 GMT

I tried Distinct and it did not work as well.

Re: Mapping date column with Cross Table

Arthur_Fong — Wed, 28 Aug 2019 05:51:01 GMT

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;

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 05:56:28 GMT

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

Re: Mapping date column with Cross Table

Arthur_Fong — Wed, 28 Aug 2019 06:09:02 GMT

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;

Re: Mapping date column with Cross Table

Arthur_Fong — Wed, 28 Aug 2019 06:12:49 GMT

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;

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 06:19:10 GMT

It worked finally !

Thanks a lot!

Re: Mapping date column with Cross Table

Arthur_Fong — Wed, 28 Aug 2019 06:24:44 GMT

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;

Re: Mapping date column with Cross Table

kaanerisen — Wed, 28 Aug 2019 06:34:42 GMT

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...

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 07:47:18 GMT

Thanks for the info!

I will do it accordingly.

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 09:31:44 GMT

I used the same script as provided by you, MonthYear field is fixed, but now its not showing Value and Rate field now.

Its not showing any field from date_2 table, except MonthYear.

check the image attached and check the update Qlik app.

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 09:29:13 GMT

I changed the table script.

now its not showing other column from Table_2.

Re: Mapping date column with Cross Table

kaanerisen — Wed, 28 Aug 2019 10:17:38 GMT

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.

Re: Mapping date column with Cross Table

LP27 — Wed, 28 Aug 2019 11:20:16 GMT

It worked! Thanks a lot!