topic Re: Problem adding a date field in my model in App Development

Problem adding a date field in my model

mtabernad — Sat, 16 Nov 2024 03:09:15 GMT

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.

Re: Problem adding a date field in my model

Lisa_P — Thu, 27 Feb 2020 21:57:48 GMT

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.

Re: Problem adding a date field in my model

prabhu0505 — Thu, 27 Feb 2020 22:57:48 GMT

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.

Re: Problem adding a date field in my model

mtabernad — Fri, 28 Feb 2020 10:40:43 GMT

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.

Re: Problem adding a date field in my model

Qlik1_User1 — Fri, 28 Feb 2020 12:57:35 GMT

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

Re: Problem adding a date field in my model

mtabernad — Fri, 28 Feb 2020 13:22:58 GMT

Yes, they are

Re: Problem adding a date field in my model

Qlik1_User1 — Fri, 28 Feb 2020 13:27:49 GMT

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

Re: Problem adding a date field in my model

mtabernad — Fri, 28 Feb 2020 13:39:02 GMT

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?

Re: Problem adding a date field in my model

Qlik1_User1 — Fri, 28 Feb 2020 13:40:08 GMT

Surely its a date format issue

Re: Problem adding a date field in my model

mtabernad — Fri, 28 Feb 2020 13:48:59 GMT

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

Re: Problem adding a date field in my model

mtabernad — Fri, 28 Feb 2020 15:29:07 GMT

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

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

Any help, please?

Re: Problem adding a date field in my model

Qlik1_User1 — Mon, 02 Mar 2020 03:41:37 GMT

Try this

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

Re: Problem adding a date field in my model

jonathandienst — Mon, 02 Mar 2020 04:58:07 GMT

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,

Re: Problem adding a date field in my model

musketeers — Mon, 02 Mar 2020 07:20:57 GMT

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

Re: Problem adding a date field in my model

mtabernad — Mon, 02 Mar 2020 07:40:10 GMT

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

Thank you very much! You made my day