Problem adding a date field in my model

[mtabernad]

Hi community,

I think it has to be easy to solve but I don't find the way to do it.

I have a Qlik Sense app. The model is quite simple. There are two tables (fact and calendar) matched by date_id field as follows:

I want to add (concatenate) to my fact table three new fields from another table. These new fields are: date_id (which it is already in the fact table) and Q_concurrent and hora_concurrencia (not already in fact table). This part of script is as follows:

After doing that, my fact table is like this (which make sense):

The problem comes when I filter by date_id (for example 25/02/2020). The part of the fact table with new rows disappears. It's like the new rows don't match with the calendar.

Before filtering by date:

After filtering by date:

Does anyone know where the problem is?

Thank you so much.

[Lisa_P]

I'm not sure what you are trying to achieve but Concatenate is designed to append data when all or most fields are the same.  Try a join or even a new table which links through the common field.

[prabhu0505]

I second @Lisa_P statements. Make sure, what you want to do, concatenate or join.

In either case confirm data types of date_id field, if required use date interpretation (date#), date, floor functions to bring them together.

[mtabernad]

Ok, concatenate doesn't make sense because both tables only share one field (date_id) and there will be too many null values. But I can't join them because they only share one field and don't have any relation among fields.

So, the other option is to match both tables separately with master calendar like this:

But I have same problem as before. If I create a table (as graphic) with my new three fields (date_id, Q_concurrent and hora_concurrencia) from "Concurrentes" table, it is shown as follows (which makes sense):

But if I filter by date_id (for example 25/02/2020), it is shown null values (I guess corresponding to lines of fact table which have  null values for fields Q_concurrent and hora_concurrencia). It seems like when I filter by date_id the model doesn't search in "concurrentes" table.

[Qlik1_User1]

Can you check if records for 25 Feb is present in Fact table

[mtabernad]

Yes, they are

[Qlik1_User1]

Are you able to filter the data ,by selecting date from calendar in Fact table? If yes, than concurent table is not linked correctly with Calendar table

[mtabernad]

Yes, I can filter data in Fact table by selecting date from Calendar.

I know that "Concurrentes" table is not linking correctly with Calendar but I don't know why. The point is to know why they are not linking properly.

Any ideas?

[Qlik1_User1]

Surely its a date format issue

[mtabernad]

Yes, probably you are right. It can't be other reason.

If I solve it, I'll post it. Meanwhile, any new format ideas are welcome