Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jeremy_fourman
Contributor

DD - Measure Field returning NULL

Hello All -

ODBC connection.

Although my need is larger I have skinnied this down to a straight table with 1 dimension and 1 measure, the measure is sum(FIELD).

Reload works fine and on state change I can utilize an ODBC trace I can see that the generated SQL is produced properly and when I execute the generated SQL directly against the database the correct totals are produced.

I can also add a table box that contains only the measure field and I see values returned to the UI. Listboxes with dimensions seem correct. I can replace SUM(FIELD) with MAX(FIELD) and it gives me the largest value from the returned rows but SUM(FIELD) does not total and only produces NULL.

Any ideas?

TIA

-Jeremy

11 Replies

Re: DD - Measure Field returning NULL

Not sure but can you try to create a straight table with Dimension as FIELD and IsNum(FIELD) as expression and see if any of the value is 0. 0 would mean that one of the values within FIELD is text and that is causing the Sum() function to not work.

sasiparupudi1
Honored Contributor III

Re: DD - Measure Field returning NULL

may be try

sum(Evaluate(trim(Field)))

krishna_2644
Valued Contributor III

Re: DD - Measure Field returning NULL

Did you aggregate  the date while summing up the field and using max(field) simultaneously?

antoniotiman
Honored Contributor III

Re: DD - Measure Field returning NULL

In DD add Group By.

jeremy_fourman
Contributor

Re: DD - Measure Field returning NULL

THanks for the quick feedback guys.

@Sunny

Good thinking . Database definition of the field is NUMBER.

The measure field when displayed within tablebox is left aligned.

When I 'double load' the measure as a dimension and do the ISNUM check, all come through as -1. QV also identifies the dimension as numeric,integer. The dimension value in listbox is right aligned.

I also tried converting the measure even though database type is numeric, but no change. So I think you may be onto something in that QV is interpreting it as a string although everything looks ok?

@Sasidhar

Wouldn't that be within load script? My script syntax here is pure SQL since no preceding loads are allowed, or am I off here.

@krishna

No I have not, let me try aggr real fast and see if there is any change.

Re: DD - Measure Field returning NULL

‌Jeremy -

I am relatively novice when it comes to Direct Discovery. So pardon me for not understanding some of the terminology here. What does 'double load' mean? Is it Direct Discovery related thing? Also, I am confused: you mentioned that in the table box the Field is left aligned, but in the list box its right aligned??? Not sure I completely understood your findings.

Best,

Sunny

jeremy_fourman
Contributor

Re: DD - Measure Field returning NULL

No worries Sunny, first time use case for DD for me as well.

Double load means I brought the same field in as a measure and a dimension.

What I was trying to say by left aligned and right aligned is QV is displaying one as a number and the other as a string. With QV though I know I can't always count on the display, it's just I can't see what type it thinks the measure value is outside of putting it in a listbox or tablebox.

@krishna

I tried aggr(max(FIELD),DIM) and get the max value, sum(aggr(max(FIELD),DIM)) and get max value. aggr(sum(FIELD),DIM) and I get null.

@antonio

Not sure what you mean by this?

Thanks for all feedback.

sun_dip08
New Contributor III

Re: DD - Measure Field returning NULL

Hey Jeremy,

How's your data model looks like? I am also working on some direct discovery scenarios. If you have a key in the direct table to join to in-memory dimension tables, be careful, as it sums the values in the key not giving the good results.

Regards,

Sandip

jeremy_fourman
Contributor

Re: DD - Measure Field returning NULL

Hi Sandip, thanks for the reply.

I know I have not shared code or a model as it is sensitive but right now I have a single direct table and nothing else.

Community Browser