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.
I think the problem is due to one or both dates having a hidden time component. Using the format functions (like Date()) can prevent the display of the time, but does not remove the time from the underlying value. Use Floor() to remove the time (and Frac() to remove the date part) if you want a time field):
Date(Floor(yourtimefield)) as Date,
Time(Frac(yourtimefeidl)) as Time,
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.
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.
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.