topic Re: Preceding load - not working in QlikView

Preceding load - not working

anagharao — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Preceding load - not working

MK_QSL — Wed, 14 Mar 2018 10:54:11 GMT

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');

Re: Preceding load - not working

anagharao — Wed, 14 Mar 2018 11:23:13 GMT

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?

Re: Preceding load - not working

MK_QSL — Wed, 14 Mar 2018 11:50:23 GMT

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.