Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FOC1346 & FOC1130 Error Messages
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] FOC1346 & FOC1130 Error Messages
 Login/Join
 
Platinum Member
posted
I am getting these error messages below. I create a hold file right before my TABLE request that uses the hold file for the report. I get these errors right at my final request that does the report. The preceding TABLE request that creates the hold file runs fine. Does anyone have any ideas.


(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1346) : FORMAT ERROR: Record 1 , Column 65602
(FOC1346) : FORMAT ERROR: Record 1 , Column 65618
(FOC1346) : FORMAT ERROR: Record 1 , Column 65634
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
-EXIT

This message has been edited. Last edited by: FP Mod Chuck,


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Difficult to say without seeing your code...
Please post it


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Code is below.

 

-SET &ECHO = 'ALL';

-DEFAULT &ST_DT = '05/05/2017;'
-DEFAULT &END_DT = '06/05/2017';
-DEFAULT &METER = '135628301';
-DEFAULT &MIN_KW_THRSH = 1.5;
-*-DEFAULT &MAX_KW_THRSH = 6;
-DEFAULT &KW_CHANNEL = 1;
-DEFAULT &KV_CHANNEL = 1;
-*-DEFAULT &OPP = 3;
-*-DEFAULT &ADJ = 5;





DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
-*
INTVL_MINS/I3 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000;


-*Minimum KW Demand Calculation - 1st Part
MIN_THRSH/D12.2 = '&MIN_KW_THRSH';
MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5  THEN (VALUE * 12) ELSE
                   IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
                   IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
                   IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
-*
-*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0;
-*
-*Maximum KW Demand (KWpeak) Calculation - 1st Part
MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5  THEN (VALUE * 12) ELSE
                   IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
                   IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
                   IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
END


-*Minimum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MIN_KW_DEM 
BY LOWEST 1 MIN_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT &MIN_KW_THRSH;
ON TABLE HOLD AS HOLDMIN 
END
-RUN

-*Read min demand into amper variable
-SET &MIN_KW_DEM = '';
-BEGIN
-READFILE HOLDMIN 
-*-TYPE &MIN_KW_DEM
-DONE

-*Maximum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MAX_KW_DEM 
BY HIGHEST 1 MAX_KW_DEM NOPRINT 
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
ON TABLE HOLD AS HOLDMAX
END
-RUN

-*Read max demand into amper variable
-SET &MAX_KW_DEM = '';
-BEGIN
-READFILE HOLDMAX 
-TYPE &MAX_KW_DEM
-DONE


DEFINE FILE MDM_READINGS ADD
-*KWH_VAL_2/D12.2 = '&KWH_VAL';
-*KVH_VAL_2/D12.2 = '&KVH_VAL';

-*KWH and KVH Totals
KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' THEN VALUE ELSE 0;
KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '&KV_CHANNEL.EVAL' THEN VALUE ELSE 0;

-*Load Factor Calculation -  Will get from other pieces of the DEFINE
-*D_LF/P33.2 = MAX_KW_DEM_2 * INTVL_HRS;
-*LD_FACT/P33.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
-*POW_FACT/P33.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2);  -*1st Formula
-*POW_FACT2/P33.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2));  -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
-*LAG_LEAD_PF/P33.3 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2));
-*LAG_LEAD_TEST/P33.3 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
-*
-*Total Usage 
TOT_USG/P33.3 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' THEN VALUE ELSE 0;
-*
-*Weekday Usage
WKDY_USG/P33.3 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI') 
                   THEN VALUE ELSE 0;
-*
-*Weekend Usage
WKEND_USG/P33.3 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' AND d_st_date_day EQ 'SAT' OR 'SUN') 
                    THEN VALUE ELSE 0;
-*
-*Missing Intervals
MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES';
-*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES';
-*
-*Notes
NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel &KW_CHANNEL partial data' ELSE
            IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel &KW_CHANNEL partial data' ELSE '';

END


TABLE FILE MDM_READINGS
SUM   KVH_VAL 
      KWH_VAL
      START_DATE_MILLIS 
      END_DATE_MILLIS
      d_st_date_cvte_hmdyy 
      d_end_date_cvte_hmdyy 
      CHANNEL
      UNIT_OF_MEASURE
      TOT_USG          AS 'Total Usage'
      WKDY_USG         AS 'Weekday Usage'
      WKEND_USG        AS 'Weekend Usage'
      NOTES            AS 'Notes'
BY AMI_METER_ID
-*BY d_st_date_cvtepoch       
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL' OR '&KV_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
WHERE MISS_INTVL EQ 'YES';  
ON TABLE HOLD AS HOLDRPT
END
-RUN


DEFINE FILE HOLDRPT
MIN_KW_DEM_2/D12.2 = '&MIN_KW_DEM';
MAX_KW_DEM_2/D12.2 = '&MAX_KW_DEM';
-*
-*H period
INTVL_HRS/D5.2 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000;
-*
-*Load Factor Calculation -  Will get from other pieces of the DEFINE
D_LF/P33.2 = MAX_KW_DEM_2 * INTVL_HRS;
LD_FACT/P33.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
POW_FACT/P33.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2);         -*1st Formula
POW_FACT2/P33.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2));  -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
LAG_LEAD_PF/P33.3 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2));
LAG_LEAD_TEST/P33.3 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
END


TABLE FILE HOLDRPT
PRINT *
END
-RUN
-EXIT



WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );

What the above is supposed to do ?
Is this a function that you've created ?

Look at each of your fields that have an error and test/verify them and the value that are assigned to them. I don't have your metadata, so you need to look at this.

Also, run only the first TABLE FILE and fix the error that may have at that step and then go to the next step.


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You might also want to use the WHENCE command to see if you have another HOLDRPT master lurking somewhere in your APP PATH.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
MartinY - Yes, this is a custom function to handle Epoch dates that we have stored in the database.

The fields are being generated in the hold file directly above the request that creates the final report. If I comment out that preceding hold file, the fields generate successfully. Are you referring to that TABLE FILE...one that generates the preceding hold file?

Also, there is not another HOLDRPT master out there. I used the WHENCE command.


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
See if removing the AS names makes a difference. If you're not using the SET ASNAMES = ON setting, you are probably getting duplicate field names in your HOLD file.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I am using SET ASNAMES=ON now and I get the same errors. Anymore ideas?


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I'm not sure what the limit is on packed (P) fields these days. It used to be 31, so P33.3 may be an issue - although that probably doesn't explain the problem with START_DATE_MILLIS and END_DATE_MILLIS. Also see if adding SET HOLDLIST = PRINTONLY helps.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
MartinY - Did I understand correctly your last comments that you posted about what you suggested I should do from the response that I posted? I want to make sure I understand what you are suggesting.


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dan S - I tried both of your suggestions....I changed the P33 to P20 and I used the SET HOLDLIST = PRINTONLY. I get the same format errors. Here is the log file below.


0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
SET ASNAMES = ON;
-DEFAULT &ST_DT = '05/05/2017';
-DEFAULT &END_DT = '06/05/2017';
-DEFAULT &METER = '135628301';
-DEFAULT &MIN_KW_THRSH = 1.5;
-*-DEFAULT &MAX_KW_THRSH = 6;
-DEFAULT &KW_CHANNEL = 1;
-DEFAULT &KV_CHANNEL = 1;
-*-DEFAULT &OPP = 3;
-*-DEFAULT &ADJ = 5;
DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
-*
INTVL_MINS/I3 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000;
-*Minimum KW Demand Calculation - 1st Part
MIN_THRSH/D12.2 = '1.5';
MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
-*
-*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0;
-*
-*Maximum KW Demand (KWpeak) Calculation - 1st Part
MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
END
-*Minimum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MIN_KW_DEM
BY LOWEST 1 MIN_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT 1.5;
ON TABLE HOLD AS HOLDMIN
END
-RUN
(FOC822) VALID VALUES ARE ASNAMES=OFF ON FOCUS MIXED FLIP SUBST
1
0 NUMBER OF RECORDS IN TABLE= 30 LINES= 1
-*Read min demand into amper variable
-SET &MIN_KW_DEM = '';
-BEGIN
(FOC2906) WARNING: &MIN_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMIN
-READFILE HOLDMIN
-*-TYPE &MIN_KW_DEM
-DONE
-*Maximum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MAX_KW_DEM
BY HIGHEST 1 MAX_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
ON TABLE HOLD AS HOLDMAX
END
-RUN
1
0 NUMBER OF RECORDS IN TABLE= 48 LINES= 1
-*Read max demand into amper variable
-SET &MAX_KW_DEM = '';
-BEGIN
(FOC2906) WARNING: &MAX_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMAX
-READFILE HOLDMAX
-TYPE 4.03
4.03
-DONE
DEFINE FILE MDM_READINGS ADD
-*KWH_VAL_2/D12.2 = '&KWH_VAL';
-*KVH_VAL_2/D12.2 = '&KVH_VAL';
-*KWH and KVH Totals
KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
-*Load Factor Calculation - Will get from other pieces of the DEFINE
-*D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS;
-*LD_FACT/P20.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
-*POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2); -*1st Formula
-*POW_FACT2/P20.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
-*LAG_LEAD_PF/P20.2 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2));
-*LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
-*
-*Total Usage
TOT_USG/P20.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
-*
-*Weekday Usage
WKDY_USG/P20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI')
THEN VALUE ELSE 0;
-*
-*Weekend Usage
WKEND_USG/P20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'SAT' OR 'SUN')
THEN VALUE ELSE 0;
-*
-*Missing Intervals
MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES';
-*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES';
-*
-*Notes
NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel 1 partial data' ELSE
IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel 1 partial data' ELSE '';
END
TABLE FILE MDM_READINGS
SUM KVH_VAL
KWH_VAL
START_DATE_MILLIS
END_DATE_MILLIS
d_st_date_cvte_hmdyy
d_end_date_cvte_hmdyy
CHANNEL
UNIT_OF_MEASURE
TOT_USG
WKDY_USG
WKEND_USG
NOTES
BY AMI_METER_ID
-*BY d_st_date_cvtepoch
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1' OR '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
WHERE MISS_INTVL EQ 'YES';
ON TABLE HOLD AS HOLDRPT
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 48 LINES= 1
DEFINE FILE HOLDRPT
MIN_KW_DEM_2/D12.2 = ' 1.59 ';
MAX_KW_DEM_2/D12.2 = ' 4.03 ';
-*
-*H period
INTVL_HRS/D6.2 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000;
-*
-*Load Factor Calculation - Will get from other pieces of the DEFINE
D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS;
LD_FACT/P20.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula
POW_FACT2/P20.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
LAG_LEAD_PF/P20.2 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2));
LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
END
SET HOLDLIST = PRINTONLY;
TABLE FILE HOLDRPT
PRINT KWH_VAL
KVH_VAL
INTVL_HRS
MIN_KW_DEM_2 AS 'Minimum kW Demand'
MAX_KW_DEM_2 AS 'Maximum kW Demand'
LD_FACT AS 'Load Factor'
LAG_LEAD_TEST AS 'Average Power Factor'
TOT_USG AS 'Total Usage'
WKDY_USG AS 'Weekday Usage'
WKEND_USG AS 'Weekend Usage'
BY AMI_METER_ID
END
-RUN
(FOC822) VALID VALUES ARE HOLDLIST=ALL ALLKEYS PRINTONLY NOFOCLIST EXPLICIT
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1346) : FORMAT ERROR: Record 1 , Column 65602
(FOC1346) : FORMAT ERROR: Record 1 , Column 65618
(FOC1346) : FORMAT ERROR: Record 1 , Column 65634
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
-EXIT


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You're getting an error on the SET ASNAMES command:

(FOC822) VALID VALUES ARE ASNAMES=OFF ON FOCUS MIXED FLIP SUBST

It's not working because you have a semi-colon at the end. Semi-colons are used to terminate -SET commands, but not plain SET commands.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
AMC,
quote:

MartinY - Did I understand correctly your last comments that you posted about what you suggested I should do from the response that I posted? I want to make sure I understand what you are suggesting.


My point was to execute only the first step of your code and look at the result since it's the first place where START_DATE_MILLIS and END_DATE_MILLIS are used.
Also take a special attention to you date function. Be sure that it work properly.
Then go step by step until you have the error.
But, according to your previous post, it seems to work ok.

quote:

(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1346) : FORMAT ERROR: Record 1 , Column 65602
(FOC1346) : FORMAT ERROR: Record 1 , Column 65618
(FOC1346) : FORMAT ERROR: Record 1 , Column 65634
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


As for the above I would also take a look at the source data itself using another tool than Focus if possible.
It is possible that your data is corrupted.

Since you are doing
TABLE FILE MDM_READINGS
 PRINT MIN_KW_DEM
 BY LOWEST 1 MIN_KW_DEM NOPRINT
 WHERE AMI_METER_ID EQ '135628301';
 WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
 WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
 WHERE CHANNEL EQ '1';
 WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
 WHERE MIN_KW_DEM GT 1.5;

What I would do is the below and take a look at the data :
TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
      END_DATE_MILLIS
      VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dan S - Oops...I forgot I added the semi-colon to the end by mistake to the SET command...going to fast between SETs and -SETs and got crossed up there. I removed it.

MarinY - So for this single query below that you said to take a look at to see the data, I get zero records returned. Here's the query with the Defines for the dates. I get no records returned. As you suggest, I also ran the SQL query version of this in my SQL tool DBVis amd I get no records. I may have expand my date range or something.

What's odd though is that I get records from the step right before this one, which has essentially the same filters where I am doing a SUM.


  
DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
END

TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
      END_DATE_MILLIS
      VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MIN_KW_DEM GT 1.5;
WHERE VALUE GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT


0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
So I changed my date range to a range that I know I should be getting records for from the database, but I get the same format errors. Also, my query right above that step indicates that it has records and it will generate record successfully when I bring back to screen. Any more ideas?

(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1346) : FORMAT ERROR: Record 1 , Column 65602
(FOC1346) : FORMAT ERROR: Record 1 , Column 65618
(FOC1346) : FORMAT ERROR: Record 1 , Column 65634
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
-EXIT


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Also, if I run the single query by itself, with a different date range for which I know records exists, it runs successfully and returns the records.

   
TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
      END_DATE_MILLIS
      VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017;';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MIN_KW_DEM GT 1.5;
WHERE VALUE GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
My 2 cents:

I'm not sure commenting like this works:
POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula

I would put it like this:
-*1st Formula
POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); 


Second, because you do not specify a HOLD format, due to your config it might be storing it as ALPHA and if a delimiter is in your data it can cause problems. Try
ON TABLE HOLD AS HOLDRPT FORMAT BINARY


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 390 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I'm not sure commenting like this works:POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula

Frans,

This style of commenting works perfectly OK and has been acceptable since 8.1.something (I think, may have been earlier?)

@ AMC,

I think this post goes to show that it is always worth breaking down the component parts of your code for testing / debugging Smiler

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Francs - I tried the FORMAT BINARY and it did not work.

I have resolved all of the FORMAT errors but two of them below.

(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
-EXIT


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783
(FOC1346) : FORMAT ERROR: Record 1 , Column 32799
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS

Have you trapped your output and opened it up in a text editor? Try using HOLD FORMAT ALPHA to enable you to actually see numerics, dates etc.

Actually checking the data that errors should be your first port of call. Knowing the attributes of your data and the values that are actually being rejected will often identify the error to you.

Is it possible that your date(time) fields are NULL? Try using SET HOLDMISS = ON and SET HOLDATTR = ON to transfer MISSING=ON and other attributes to your hold file.


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Tony A - I inspected the date-time fields (which they are epoch dates that are stored as integer fields in the database) within the data set at hand and I don't see any nulls or anything odd.

Well I decided to take the route of reading my epoch date values into amper variables and Define them in the following HOLD file (HOLDRPT). I get this error on my last TABLE request that does the report: (FOC1065) PROGRAM ERROR

Here is the log now:

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
SET ASNAMES = ON
-DEFAULT &ST_DT = '02/01/2017';
-DEFAULT &END_DT = '02/04/2017';
-DEFAULT &METER = '135628301';
-DEFAULT &MIN_KW_THRSH = 1.5;
-*-DEFAULT &MAX_KW_THRSH = 6;
-DEFAULT &KW_CHANNEL = 1;
-DEFAULT &KV_CHANNEL = 1;
-*-DEFAULT &OPP = 3;
-*-DEFAULT &ADJ = 5;
DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
-*
INTVL_MINS/I20 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000;
-*Minimum KW Demand Calculation - 1st Part
MIN_THRSH/D12.2 = '1.5';
MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
-*
-*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0;
-*
-*Maximum KW Demand (KWpeak) Calculation - 1st Part
MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
END
-*Minimum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MIN_KW_DEM
BY LOWEST 1 MIN_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT 1.5;
ON TABLE HOLD AS HOLDMIN
END
-RUN
1
0 NUMBER OF RECORDS IN TABLE= 67 LINES= 2
-*Read min demand into amper variable
-SET &MIN_KW_DEM = '';
-BEGIN
(FOC2906) WARNING: &MIN_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMIN
-READFILE HOLDMIN
-*-TYPE &MIN_KW_DEM
-DONE
-*Maximum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MAX_KW_DEM
BY HIGHEST 1 MAX_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
ON TABLE HOLD AS HOLDMAX
END
-RUN
1
0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1
-*Read max demand into amper variable
-SET &MAX_KW_DEM = '';
-BEGIN
(FOC2906) WARNING: &MAX_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMAX
-READFILE HOLDMAX
-TYPE 10.86
10.86
-DONE
DEFINE FILE MDM_READINGS ADD
-*KWH_VAL_2/D12.2 = '&KWH_VAL';
-*KVH_VAL_2/D12.2 = '&KVH_VAL';
-*KWH and KVH Totals
KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
-*Load Factor Calculation - Will get from other pieces of the DEFINE
-*D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS;
-*LD_FACT/P20.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
-*POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2); -*1st Formula
-*POW_FACT2/P20.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
-*LAG_LEAD_PF/P20.2 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2));
-*LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
-*
-*Total Usage
TOT_USG/D20.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0;
-*
-*Weekday Usage
WKDY_USG/D20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI')
THEN VALUE ELSE 0;
-*
-*Weekend Usage
WKEND_USG/D20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'SAT' OR 'SUN')
THEN VALUE ELSE 0;
-*
-*Missing Intervals
MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES';
-*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES';
-*
-*Notes
NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel 1 partial data' ELSE
IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel 1 partial data' ELSE '';
-*START_DATE_MILLIS/P39 = START_DATE_MILLIS;
-*END_DATE_MILLIS/P39 = END_DATE_MILLIS;
END
SET MISSING = ON
TABLE FILE MDM_READINGS
SUM KVH_VAL
KWH_VAL
-* START_DATE_MILLIS
-* END_DATE_MILLIS
d_st_date_cvte_hmdyy
d_end_date_cvte_hmdyy
CHANNEL
UNIT_OF_MEASURE
TOT_USG
WKDY_USG
WKEND_USG
NOTES
BY AMI_METER_ID
-*BY d_st_date_cvtepoch
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1' OR '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
WHERE MISS_INTVL EQ 'YES';
ON TABLE HOLD AS HOLDRPT FORMAT ALPHA
END
-RUN
(FOC822) VALID VALUES ARE MISSINGTEST=OLD SPECIAL
0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1
-*Hold epoch dates
TABLE FILE MDM_READINGS
SUM START_DATE_MILLIS
END_DATE_MILLIS
BY AMI_METER_ID NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1' OR '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MISS_INTVL EQ 'YES';
ON TABLE HOLD AS HOLDDTS
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1
-*Read epoch dates into amper variables
-SET &START_DATE_MILLIS = '';
-SET &END_DATE_MILLIS = '';
-BEGIN
(FOC2906) WARNING: &START_DATE_MILLIS WILL BE OVERWRITTEN BY -READFILE HOLDDTS
(FOC2906) WARNING: &END_DATE_MILLIS WILL BE OVERWRITTEN BY -READFILE HOLDDTS
-READFILE HOLDDTS
-TYPE 396774493200000
396774493200000
-TYPE 396774730800000
396774730800000
-DONE
-RUN
DEFINE FILE HOLDRPT
MIN_KW_DEM_2/D20.2 = 1.56 ;
MAX_KW_DEM_2/D20.2 = 10.86 ;
START_DATE_MILLIS/P33 = 396774493200000;
END_DATE_MILLIS/P33 = 396774730800000;
-*
-*D_START_DATE_MILLIS/I20 = EDIT(START_DATE_MILLIS);
-*D_END_DATE_MILLIS/I20 = EDIT(END_DATE_MILLIS);
-*H period
INTVL_HRS/I20 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000;
-*INTVL_HRS/I20 = (D_END_DATE_MILLIS - D_START_DATE_MILLIS) / 3600000;
-*INTVL_HRS/I20 = (8000000000 - 4000000000) / 3600000;
-*
-*Load Factor Calculation - Will get from other pieces of the DEFINE
D_LF/D20.2 = MAX_KW_DEM_2 * INTVL_HRS;
LD_FACT/D20.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
POW_FACT/D20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula
POW_FACT2/D20.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
LAG_LEAD_PF/D20.2 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2));
LAG_LEAD_TEST/D20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
END
TABLE FILE HOLDRPT
HEADING CENTER
"Min Max Summary"
PRINT KVH_VAL
KWH_VAL
INTVL_HRS
D_LF
MIN_KW_DEM_2 AS 'Minimum kW Demand'
MAX_KW_DEM_2 AS 'Maximum kW Demand'
LD_FACT AS 'Load Factor'
LAG_LEAD_TEST AS 'Average Power Factor'
TOT_USG AS 'Total Usage'
WKDY_USG AS 'Weekday Usage'
WKEND_USG AS 'Weekend Usage'
NOTES AS 'Notes'
BY AMI_METER_ID
-*BY d_st_date_cvtepoch
-*WHERE AMI_METER_ID EQ '&METER';
-*WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
-*WHERE d_end_date_cvte_hmdyy LE '&END_DT';
-*WHERE CHANNEL EQ '&KW_CHANNEL' OR '&KV_CHANNEL';
-*WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MISS_INTVL EQ 'YES';
ON TABLE PCHOLD FORMAT AHTML
ON TABLE NOTOTAL
ON TABLE SET AUTOFIT OFF
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET WEBVIEWTARG OFF
ON TABLE SET WEBVIEWER ON
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN
(FOC1065) PROGRAM ERROR
-EXIT


WF 8.1.05
Windows
SQL Server 2014
HTML, PDF, EXCEL
 
Posts: 175 | Registered: March 24, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi AMC

I think you should open a case with techsupport on this one...


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1566 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FOC1346 & FOC1130 Error Messages

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.