Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
mtabernad
Partner - Creator
Partner - Creator

Problem adding a date field in my model

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:

capture38.png

 

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:

capture34.png

 

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

capture39.png

 

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:

capture36.png

 

After filtering by date:

 

capture37.png

 

Does anyone know where the problem is?

 

Thank you so much.

Labels (2)
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

14 Replies
Lisa_P
Employee
Employee

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

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
Partner - Creator
Partner - Creator
Author

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:

capture41.png

 

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):

 

capture42.png

 

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.

capture43.png

Qlik1_User1
Specialist
Specialist

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

mtabernad
Partner - Creator
Partner - Creator
Author

Yes, they are

 

capture44.png

Qlik1_User1
Specialist
Specialist

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
Partner - Creator
Partner - Creator
Author

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

Surely its a date format issue

mtabernad
Partner - Creator
Partner - Creator
Author

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