To homepage To homepage
Solved

Problem with project variable used in semantic model

  • 6 December 2023
  • 7 replies
  • 85 views
christian.koeken
Rank
Badge

Dear Support,

TimeXtender version: 20.10.43.64

In TimeXtender we have two project variables that we use in a data selection rule in a semantic model.

In attachment "project variables settings” you'll see the settings of the two variables.

The dynamic variable script that it has to execute is:

SELECT CAST(MAX(LoadingDate) AS DATE) FROM FinFact.FacturatieControle to get the highest date. The other script will get the lowest date.

In attachment "semantic model with data selection rule” you'll see the data selection rule in the semantic model.

 

We want to filter the date dimension so that it only shows the dates which are in the fact table (FacturatieControle is the fact table).

The problem now is that the project variable won't update although we set the resolve type at 'Every time’.

Only a deploy and execute of the model will update the date filter.

 

Can you help me with this issue?

 

Best regards,

Christian Koeken

 

icon

Best answer by Thomas Lind 11 December 2023, 14:19

View original
2 Attachments

7 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @christian.koeken 

May I ask why you would like to filter the date dimension so that it only shows the dates which are in the fact table? In other words, what is the outcome you are looking to achieve?

I recreated your setup on my end now, and the semantic model does need to be executed in order for the values of the dynamic variable to have an effect in the semantic model (however a deploy does not seem to be necessary). I would expect this behavior, as the tabular model needs be processed for the changes to take effect.

R
Rank
Userlevel 2

Hi Christian

I think a new execute (maybe a deploy as well?) is always necessary in this case. Technically the project variable does update, but your tabular model has no knowledge of that.

christian.koeken
Rank
Badge

Hi @Christian Hauggaard,

I want to achieve that the date filter in the Qlik report only shows the dates which are in the fact table. Our date dimension has also the year 2024 in it. So, the user of the Qlik report sees 2024 too if we don't filter the date dimension. 

 

rory.smith
Rank
Userlevel 5
Badge +7

Hi Christian(s),

in Qlik models it is quite common to reduce your dimensions down to the matching elements coming from fact tables. If you have a situation where you do not want to (or cannot) reload your Qlik apps from TimeXtender, it would be nice for the views generated to cover the Qlik script to be able to handle this. As the Qlik Sense endpoint talks directly to the engine API you sometimes need to handle reloading from Qlik to avoid bypassing load balancer rules. Qlik Cloud and QlikView are different yet again.

 

There is actually a similar request from years ago in the same context: 

 

You can do this kind of thing with set analysis in Qlik, but managing that from TimeXtender would require the pre- and post-script function for Qlik models to be brought back. Avoiding the need for Qlik script or front-end syntax development is important: if you need to do scripting in Qlik, it quickly becomes a slippery slope with the risk of ETL popping up in Qlik again vs. being handled centrally in TX.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @christian.koeken 

I thought about this issue.

I think it may be about the context not including the SSL instances.

When you preview the custom selection rule

And click on Show Translation you will see the following message.

If you click yes, it will attempt to resolve the variable like so.

If you do this, does it show the correct date?

christian.koeken
Rank
Badge

Hi @Thomas Lind ,

 

Yes, it shows the correct date if I click on 'Show Translation’ and click yes.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @christian.koeken 

OK, but it still does not apply this date when you run the query.

I have an idea.

If you add two custom fields in the Date table a max and min date field. You then add the custom variables to either one. When you have this as a field in the date table you can add the two fields

I also chose to make it an OR statement instead of an AND, but I don’t think it would make a difference.

Does this work?

Reply


Terms and ConditionsCookie settings