Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeremy_fourman
Creator
Creator

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
sunny_talwar

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
Master III
Master III

may be try

sum(Evaluate(trim(Field)))

krishna_2644
Specialist III
Specialist III

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

antoniotiman
Master III
Master III

In DD add Group By.

jeremy_fourman
Creator
Creator
Author

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.

sunny_talwar

‌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
Creator
Creator
Author

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.

Anonymous
Not applicable

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
Creator
Creator
Author

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.