Preceding load - not working

[anagharao]

Hi

I created a subroutine to find the maximum and minimum date in any field, but the preceding load is not working. Anything wrong with the below code?

SUB CALFROMFIELD(_Field)

LOAD MIN(DT) AS MIN_DT,

          MAX(DT) AS MAX_DT

          GROUP BY DT;

LOAD

          DATE(NUM(FIELDVALUE('$(_Field)',ROWNO()))) AS DT

          AUTOGENERATE FIELDVALUECOUNT('$(_Field)');

END SUB

Orders:

LOAD *, 1 as OrderCounter INLINE [

OrderId, OrderDate, Quantity

1, 01/01/2012, 101

2, 01/01/2012, 102

3, 02/01/2012, 103

4, 03/01/2012, 104

5, 03/03/2012, 105

6, 03/04/2012, 106

];

CALL CALFROMFIELD('OrderDate', 'OrderCalendar', 'Order ');

I've tried this without the GROUP BY clause, but the results are the same.

[MK_QSL · Accepted solution]

Use something like this..

SUB CALFROMFIELD(_DateField,_CalendarName,_Prefix)
 "$(_CalendarName)": 
 LOAD
 Date($(_DateField)) as $(_DateField),
 Year($(_DateField)) as  $(_Prefix)Year,
 Month($(_DateField)) as  $(_Prefix)Month
 ;
 LOAD
 MIN_DT + IterNo() - 1 as $(_DateField)
 While MIN_DT + IterNo() - 1 <= MAX_DT;
 LOAD
 Min(Floor(FieldValue('$(_DateField)',RecNo()))) as MIN_DT,
 Max(Floor(FieldValue('$(_DateField)',RecNo()))) as MAX_DT
 AUTOGENERATE FIELDVALUECOUNT('$(_DateField)');
END SUB

Orders:
LOAD 
 *, 
 1 as OrderCounter 
INLINE 
[
 OrderId, OrderDate, Quantity
 1, 01/01/2012, 101
 2, 01/01/2012, 102
 3, 02/01/2012, 103
 4, 03/01/2012, 104
 5, 03/03/2012, 105
 6, 03/04/2012, 106
];

CALL CALFROMFIELD('OrderDate','MasterCalendar','Order');

[anagharao]

Thank you !

i had a few questions on that:

- Would it be possible to stop after finding the Min and Max date?

- Why find the min and max of every date in the field?

[MK_QSL]

We can stop after finding Min and Max Date...

We have to find min and max date to generate all the dates between them.

Say you have min date of Jan 2018 and max date of Mar 2018 but in between you have few dates missing, then  you can create those dates using this iteration loop.