topic Re: Issue with an expression calculation in QlikView

Issue with an expression calculation

Anonymous — Thu, 22 Mar 2018 09:11:02 GMT

So I'm having a table in which I'm calculating the amount of worked days filtered by the type of the activity. The expression formula is correct(I've checked with the database).
One only had activities during the third work cycle and the QlikView auto-completes the missing values(the missing values of the first and second cycle) with 0.
My issues are:
-when I'm exporting the table in Excel via a macro(I have to do extra formatting so using the built-in method would take too much time), the 0 values that are replacing the missing values are not pasted
-because of that, my expressions that calculate the amount of worked days summed for all 3 cycles return 0(this happens in my pivot tables from QlikView too)
I have the "Suppress 0 values" option unchecked and I auto-populate the missing or null values with 0.
I assume the problem is that QlikView recognizes 0 as a char/string instead of int.
So my questions are:
-Is there any way I can make QlikView recognize 0 as an integer?

-Alternatively, as a work-around the problem with the totals , how can I make a Set Expression in which a certain table column must be populated with any value? e.g: something like cycle_value -={' '}

Re: Issue with an expression calculation

YoussefBelloum — Thu, 22 Mar 2018 09:16:21 GMT

Hi,

would you be able to share some data here ? or a sample QVW ?

Re: Issue with an expression calculation

Anonymous — Thu, 22 Mar 2018 09:21:34 GMT

Would the table exported in Excel help?

Re: Issue with an expression calculation

YoussefBelloum — Thu, 22 Mar 2018 09:53:17 GMT

In this case no.. because on the charts, if a NULL a blank is replaced by a char or a numeric value, it will change the original calculations... I was talking about the source, what you really have before you load

Re: Issue with an expression calculation

Anonymous — Thu, 22 Mar 2018 10:04:16 GMT

I can't really share the database dump due to privacy issues.
Anyway, I'm having this following expression:
(Count({<ambiente = {'Ambulatorio ASL'}, Cycle = {'Cycle1'}, macro_area_codice = {'Sud'},role -= {'3'} >} DISTINCT assign_date))
How can I change the set expression so that it counts all the assign_dates that have any value different from null in the Cycle field? It would help me a lot with my current problem

Re: Issue with an expression calculation

YoussefBelloum — Thu, 22 Mar 2018 10:08:34 GMT

You can try this:

(Count({<assign_date={"=len(trim(assign_date))<>0"},ambiente = {'Ambulatorio ASL'}, Cycle = {'Cycle1'}, macro_area_codice = {'Sud'},role -= {'3'} >} DISTINCT assign_date))

Re: Issue with an expression calculation

Anonymous — Thu, 22 Mar 2018 10:12:07 GMT

I'm looking more for something like: (Count({<ambiente = {'Ambulatorio ASL'}, Cycle -= {'<however QlikView is interpreting Null>'}, macro_area_codice = {'Sud'},role -= {'3'} >} DISTINCT assign_date))
EDIT: I've found a solution by modifying the loading script. Thanks for the help anyway, I'll let this discussion as Assumed Answered

Re: Issue with an expression calculation

YoussefBelloum — Thu, 22 Mar 2018 10:17:53 GMT

you asked for this:

How can I change the set expression so that it counts all the assign_dates that have any value different from null in the Cycle field?

I give you this: assign_date={"=len(trim(assign_date))<>0"}

Can you attach some rows on an excel file to see what you have exactly on every column ?

Re: Issue with an expression calculation

Anonymous — Thu, 22 Mar 2018 10:25:57 GMT

See the excel file I have attached in the replies

Re: Issue with an expression calculation

YoussefBelloum — Fri, 23 Mar 2018 08:59:17 GMT

Hi,

I just had a look at your file, I think it is a completely different file from what you described above..