Peek returns <NULL> on count-attribute (Count & Peek issue)

[erp_curaden]

Hi everyone,

I ran into an issue where whatever I do I always get <NULL> values back. See the following code:

For i=0 to (NoOfRows('CustomerAZT1')-1)

Let currentCustomerID=Peek('CustomerID', $(i), 'CustomerAZT1');
Let currentCustomerEMail=Peek('CustomerEMail', $(i), 'CustomerAZT1');

Set minYear=2018;
Set maxYear=year(now(0));
Set minMonth=1;
Set maxMonth=12;

For j=$(minYear) to $(maxYear)
For k=$(minMonth) to $(maxMonth)
t1:
NoConcatenate
Load
Count(OrderEMail) as "NumberOfOrders"
Resident OrderAZ
Where year(OrderTimeStampAdded)&num(month(OrderTimeStampAdded))<=$(j)$(k) and $(currentCustomerID)=OrderCustomerID;

Let currentNumberOfOrders=Peek('NumberOfOrders', 0, 't1');
Drop Table t1;

Concatenate
LOAD * Inline [
'CustomerID', 'CustomerEMail', 'YearMonth', 'TotalNumberOfOrders', 'CustomerGroup'
$(currentCustomerID), $(currentCustomerEMail), $(j)$(k), $(currentNumberOfOrders), $(currentCustomerGroup)
];

Next
Next
Next;

I count the number of orders for each customer. But somehow if there is no order for a customer, the "peek"-function returns <NULL> instead of 0.

Any ideas would be much apreciated. Thank you in advance for looking through my code 🙂

[treysmithdev · Accepted solution]

I would suggest changing:

Let currentNumberOfOrders=Peek('NumberOfOrders', 0, 't1');

to:

Let currentNumberOfOrders=Alt(Peek('NumberOfOrders', 0, 't1'),0);

The reason you are getting NULL is because the where clause is executing before the Count(). So the Count() function doesn't even get executed because it isn't fed any data, which results with a table with no rows. Try the test case below to see what I mean:

Test:
Load * Inline [
ID, Cat, Amt
1, A, 10
1, B, 20
2, C, 30
];


WhereTest:
Load
	'Test' 		as Note,
	Count(Amt) 	as Cnt
Resident
	Test
Where
	ID = 1 AND Cat = 'C';