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: 
Not applicable

Help with debugging script

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

9 Replies
Anonymous
Not applicable
Author

Hi

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Does RefEngy have the same number of rows as RefEngy1?

-Rob

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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

tresesco
MVP
MVP

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

sss.png

Not applicable
Author

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

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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