topic Re: Help with debugging script in QlikView

Help with debugging script

Thu, 30 Jan 2014 12:08:31 GMT

I have the following script snippet:

...

[RefEngy1]:

LOAD

Month,

ContractYr1Pctg,

Capacity

...

[RefEngy]:

LOAD

Month,

if (Month = 'Jan', ContractYr1Pctg*Capacity, 'No Value') as [Ref Energy]

Resident RefEngy1;

The script loads fine, and displays a value of 'Jan' for Month and has values for the other fields, but Ref Energy is showing as '-' in the TableBox. Even if the month is anything other than Jan, I should see 'No Value' in Ref Energy??

Appreciate any ideas

Thanks

Re: Help with debugging script

Anonymous — Thu, 30 Jan 2014 12:14:38 GMT

What happens if you use numeric 0 instead of 'No Value' ? As in :

if (Month = 'Jan', ContractYr1Pctg*Capacity, 0 ) as [Ref Energy]

Best Regards, Bill

Re: Help with debugging script

rwunderlich — Thu, 30 Jan 2014 12:20:18 GMT

Does RefEngy have the same number of rows as RefEngy1?

-Rob

Re: Help with debugging script

Peter_Cammaert — Thu, 30 Jan 2014 12:38:54 GMT

Works for me (see below).

Some suggestions:

Do your Month values have leading or trailing spaces? Use trim() to errr...trim.
Do both fields ContractYr1Pctg & Capacity contain valid numbers? Open Table Viewer and check that their contents is aligned to the right
Package those two tables and the corresponding script part in a reduced document and post it here.

Good luck,

Peter

Re: Help with debugging script

Fri, 31 Jan 2014 06:06:14 GMT

Peter,

Thank you. Sorry, I have a very basic question!! How do I upload my script in response to your post? I am seeing 'Insert Video/Image/Link', but no 'Insert File' option?

Thanks

Re: Re: Help with debugging script

tresB — Fri, 31 Jan 2014 06:41:08 GMT

Click on 'Use advanced editor' -> bottom right -> attach

Re: Re: Re: Help with debugging script

Fri, 31 Jan 2014 08:02:52 GMT

Great. Thanks.

I have attached my script. In [RefEngy1], I am getting the Date and constant values. In [REFENGY], I am trying to calculate the Reference Energy, based on what month it is , it should choose a different constant for Reference Insolation.

Even though it is showing the month correctly as 'Jan', it is not producing any values for the Ref Energy, showing up as '-'.

I tried trimming and the constants are numeric.

Appreciate any help

Thanks

Re: Help with debugging script

Fri, 31 Jan 2014 08:17:23 GMT

Hi,

I go with Bill Markham's track, [Ref Energy]'s calculation mixes text and numeric result types so it doesn't work.

You can make [Ref Energy] all numeric:

if (Month = 'Jan', ContractYr1Pctg*Capacity, 0) as [Ref Energy]

Or you can make it all text:

if (Month = 'Jan', Text(ContractYr1Pctg*Capacity), 'No Value') as [Ref Energy]

Or you can have both types by using Dual() function:

if (Month = 'Jan', Dual(Text(ContractYr1Pctg*Capacity),ContractYr1Pctg*Capacity),

Dual('No Value',0)) as [Ref Energy]

Hope this helps

Re: Help with debugging script

rwunderlich — Fri, 31 Jan 2014 13:10:52 GMT

The script you uploaded is quite different than what you posted. The script is calculating RefEnergy using pick(), not if().

In your pick, I think you may need to force the month to be numeric.

Pick (num(Month(Date)),...

-Rob

Re: Re: Re: Help with debugging script

Fri, 31 Jan 2014 15:39:59 GMT

The problem looks to be because the fields ContractYear1Pctg, Capacity are not integers.

When I say,

if (num(Month(Date))= 1, 23,0) as [RefEnergy], I get a value of 23 correctly for Jan, but

If (num(Month(Date))=1,ContractYear1Pctg*Capacity,0) is giving '-' The values for ContractYear1Pctg is 0.794 and Capacity is 5.81. In the tablebox property, I had described these fields as number with precision of 10.

How do I force it to take decimal values?

Thanks