Cannot combine fields from two tables with date dimension

[Anonymous]

Hello,

the symptom is as follows:

I have a table with the fields: A(calDate, Series, Cost) and made a line chart Sum(Cost) vs. calDate. I have several rows with the same calDate. All fine.

Then I added table B (calDate, Series, earnings,<and others>). The dates are all of the format YYYY-MM-01.

Now I wanted a line chart with two lines: cost and earnings over date and added the "earning" the same as "cost" before.

I found that since I added B I have strange values like (47658) in my calDate list box and they also appear on the X-axis.

Why can QlikView not match the dates?

I see in the table structure that a synthetic key out of calDate/Series is generated. Has this something to do with it?

How to get the expected behavior?

Thank you for any help.

[Anonymous]

Hi Tobias,

You have two common columns in this two tables. That's why you are getting Synthetic key. Take the combination of two columns Date & Series as <NewColumn> or you can remove one of the column in one table.

Regards,

Sadasiva

[Anonymous]

Or you can concatenate those two tables in to one..

use concatenate between two tables.

[Anonymous]

Synthetic keys  . How to remove them

this may helps

[jonathandienst]

I assume that you are loading the two tables from different sources, or the load logic for the date field is different. So one is a formatted date numeric, like 2017-08-01,  while the other is an unformatted date numeric like 47658. Ensure that the load logic formats both dates the same way.

The synthetic key is caused by having more than one common field - this may not be a problem and is certainly not the cause of your problem. If you want to remove it, you could create your own composite key, or if all the possible values of date and Series exist in one table, remove the redundant Series from the load of the other table.

[balar025]

might be a date format is different.

And for syn key, you have common field between two tables.

2 option:

1) concate both table

2) Create master calendar.

or can you please share your sample table structure for better understanding and giving better approach?

-Ravi

[Anonymous]

Covert dates to same format before join. Dates in Table B are seems to be in number format. hence are shown in 47658 like this number form.

[gsbeaton]

Aside from any issues with a synthetic key that sadasiva‌ has noted, Qlik is having trouble interpreting your dates as they are loaded in.  The number 47658 is the date serial number of 2030-06-24.  Have a look at this help file for more details about how Qlik interprets and stores dates.

My conclusion would be that the dates in your table A and B are stored in different formats.  Have a closer look at them, then maybe use something like the ALT() function to make sure Qlik is interpreting your dates correctly.

alt(
  date#( date , 'YYYY-MM-DD' )
 ,date#( date , 'YYYY/MM/DD' )
 ,date#( date , 'YY-MM-DD' )
 ,'No valid date')

Hope this helps

George