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)
14 Replies
mtabernad
Partner - Creator
Partner - Creator
Author

The field from I have to create the date_id field is this one (I think in string format):

capture45.png

 

I'm not able to create a date_id field with this format: 'DD/MM/YYYY'

Any help, please?

 

 

 

Qlik1_User1
Specialist
Specialist

Try this

DATE(NUM(date_id),'DD/MM/YYYY')

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
musketeers
Creator
Creator

Hi, I think the real question is to concatenate the third table which has three field with the Fact table and the common field is Date ID in both tables. I think concatenate  is not the correct solution for such case. Have you tried Apply Map feature. If not then:  Just create two mapping tables from the third table with combination of  Date ID and  Q_concurrent  AND Date ID and hora_concurrencia.

Now from Third table you have two mapping table and use apply map in fact table to get Q_concurrent and hora_concurrencia field based on Date ID field.

Thanks

Rajneesh

mtabernad
Partner - Creator
Partner - Creator
Author

Yessss!!! Finally I get it! That was the problem!

 

Thank you very much! You made my day