Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
erp_curaden
Contributor II
Contributor II

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

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 🙂

Labels (5)
1 Solution

Accepted Solutions
treysmithdev
Partner
Partner

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

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

1 Reply
treysmithdev
Partner
Partner

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

 

Blog: WhereClause   Twitter: @treysmithdev