Solved: Re: Problem adding a date field in my model - Qlik Community

mtabernad · ‎2020-02-27

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.

jonathandienst · ‎2020-03-01

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Lisa_P · ‎2020-02-27

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 · ‎2020-02-27

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 · ‎2020-02-28

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 · ‎2020-02-28

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

mtabernad · ‎2020-02-28

Yes, they are

Qlik1_User1 · ‎2020-02-28

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 · ‎2020-02-28

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 · ‎2020-02-28

Surely its a date format issue

mtabernad · ‎2020-02-28

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