Querying a field without selecting it - Qlik Community

Report Inappropriate Content · ‎2011-11-22

his Hi, I am trying to come up with a way to localize formats and dates in the documents, so far I have the formats completed, but I still have a problem with dates.

If you enter this string as a format "'WWW, MMM D, YYYY'" it will show day name, month name, day number, and year number.

What I am trying to localize are both daynames and monthnames, so i have created a couple of tables to at least try to solve the month names.

the tables are Languages and MonthNames

Then then the user selects a language, I can drice the rest of the date parts from the tables, the problem that I am having is that I cant seem to create a set analysis script that will allow me to "query" the actual MonthNames table and ask for the name of the month for the selected language, for the selected date, but since I dont have a month selected, I cant seem to select the Month name without actually selecting it.

I am building a custom function that will accept the date and it will look internally for the selected language and do the rest.

if(
     LanguageID = FieldIndex('Locale',GetFieldSelections(Locale))
     and MonthID = Num(Month(Date))
     ,MonthName
     ,0
)

This goes fine, for the languageID, since I have it selected, but since I dont have the month Selected somewhere, no MonthName is returned.

I am attaching a test document that describes exactly my issue, it seems to be pretty easy, but I can't seem to find the current way to pull the month name.

Thanks in advance for the time

-ed

ToniKautto · ‎2011-11-22

Have you considered the option to change the value of the MonthNames variable through a triggered action when you select a new language? This variable is used by the built in date functions and would give the right names automagically if you change the variable.

Report Inappropriate Content · ‎2011-11-22

Hi Toni, I have done that, and for some reason, QV doenst take the new values into account.

ToniKautto · ‎2011-11-22

Yes, I see now after testing that the variable does not affect the formatting. I will check this and get back to you later this week.

Report Inappropriate Content · ‎2011-11-22

Thanks a lot Toni!

ToniKautto · ‎2011-11-23

I have reported this as a bug with ID 44092, suggesting that there are three alternatives for an expected solution on chnaging the system variables.

- the format change is reflected in function calls

- it should not be possible to change the system variable from the front end

- system variable change can be done via function call

I will provide an update as soon as there is any feedback from R&D. In meanwhile you can of course continue implementing a solution based on language related tables.

---

Back to your initial question.

Running the expression in a text object means that if will be calculated towards the total data set, since there are no dimensiosn to evaluate the expression towards in a text object. Considering this, what will a fieldname return as comparasment value in the IF clause? The field value will only expand in to an actual value it the current selection makes the content of the field to contain a distinct value, which you can see if you make a text box with for example the expression =LanguageID . This textbox will only display a value when there is only one possible or selected value in the field.

My suggestion in this type of case is to split up the expression in different object so that you can follow the condition during your selections in order to ananlyse it behaviour, as you can see in the attached example there will be NULL values interfering in most scenarios. An if statement like this would be more sucessful in a chart object being calculated over suitable dimensions.

In the example QVW you can find a set analysis test to convert a date in to the corresponding language based month name. Here you will see that the month id can be calculated sucesfully as well as the set analysis with statically set month ID. The dollar expanded month id calculation however fails due to the dollar expansion being done over the enitre datat set and not only row by row.

A way forward on this would instead be to remodel the data a bit and link the tranlation to the data.

First step is to join the language and month value into one table, as I do not see the purpose of keeping them separated. Notice that this does not mean that the data source need to change, it is only the way the data is storeed in the QVW that is affected.

Secondly I will assume there is always a selected language, so I force a language selection in the application by making the langiage selected in a listbox. Now the language parameter can be ignore din the set analysis, as it is always made in the current selection.

To get around the dollar expansion issue it is easiest to link the month ID to the table that holds the date value. So this has been done in the Sales table as a simple preceeding load. Now the MonthName can be fetched based on the current dimension in the chart.

Report Inappropriate Content · ‎2011-11-23

Thanks a lot Toni, I'll check your suggestions and see what I can come up with.

-ed

Report Inappropriate Content · ‎2011-11-29

Toni, did you heard something back from the R&D team?

Regards

-Ed

ToniKautto · ‎2011-11-30

No reply yet, it can take a while since R&D has just be very busy with QV11 release and not is preparing the release of QV10 SR4. Bu t I will let you know as soon as I hear anything.

ToniKautto · ‎2012-02-03

It is unfortunately concluded that the option of changing the MonthNames (and the other system variables) in runtime is not possible due to limitations in the the current underlying mechanisms for handling the system variables.

R&D is however well aware of the issues and the implications of this, so they are actively working on redesigning the related parts for coming major releases of QlikView. This means that you could expect improvements within this area in QlikView 12, even though no guarantee for this can be given at this point in time.