topic Re: DD - Measure Field returning NULL in QlikView

DD - Measure Field returning NULL

jeremy_fourman — Wed, 19 Aug 2015 18:52:16 GMT

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

Re: DD - Measure Field returning NULL

sunny_talwar — Wed, 19 Aug 2015 18:55:36 GMT

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.

Re: DD - Measure Field returning NULL

sasiparupudi1 — Wed, 19 Aug 2015 19:03:04 GMT

may be try

sum(Evaluate(trim(Field)))

Re: DD - Measure Field returning NULL

Qrishna — Wed, 19 Aug 2015 19:11:55 GMT

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

Re: DD - Measure Field returning NULL

antoniotiman — Wed, 19 Aug 2015 19:22:46 GMT

In DD add Group By.

Re: DD - Measure Field returning NULL

jeremy_fourman — Wed, 19 Aug 2015 19:38:46 GMT

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

sunny_talwar — Wed, 19 Aug 2015 19:50:07 GMT

‌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

Re: DD - Measure Field returning NULL

jeremy_fourman — Wed, 19 Aug 2015 20:07:39 GMT

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.

Re: DD - Measure Field returning NULL

Anonymous — Wed, 19 Aug 2015 20:13:53 GMT

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

Re: DD - Measure Field returning NULL

jeremy_fourman — Wed, 19 Aug 2015 20:23:49 GMT

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.

Re: DD - Measure Field returning NULL

sunny_talwar — Wed, 19 Aug 2015 23:12:56 GMT

What if you specify to QlikView what the field is:

Num#(FIELD, 'Format') as FIELD

Re: DD - Measure Field returning NULL

Anonymous — Thu, 17 Dec 2015 13:07:49 GMT

I have been dabbling a bit with DD mostly on trial and error basis so I _can_ be fishing in the wrong pond here...

My discoveries (pun intended) is that you have to aggregate. A measure does not seem to be able to have multiple values (be on several lines). That's why it works when using max() as Max() gives you one value only.

I'm sorry if I'm not very clear, but I don't quite understand this enough to express myself clearer. 🙂 For what it's worth, I hope this helps.

Maybe you need to sum the lines in the db using native (sql) commands.