topic Duplicates - Need an aggr function or set analysis - help?? in QlikView

Duplicates - Need an aggr function or set analysis - help??

Wed, 06 Oct 2010 20:09:35 GMT

Hi All,

Lets say I have two tables. One for customers and one for orders. In the customers table there is no date, but in the orders table there is an order date. I have a chart where I count cumulative order ids by their order date. I would now like to add in logic to show my cumulative customers as well- but since a customer may have many orders, I am only interested in counting their customer id when they first order. I have the following statement written which would return the first order date for a customer id:

aggr(min(ORDER_DATE),Customer_ID)

Now I am having trouble with the count part. I only want to count the customer id when the order date = aggr(min(ORDER_DATE),Customer_ID) , I have tried something like the following but its not working out:

COUNT(IF(aggr(min(ORDER_DATE),Customer_ID)=ORDER_DATE,Customer_ID))

Any suggestions or advice on what I am doing wrong?? The chart only uses the order date dimension.

Thanks,

Julia

Duplicates - Need an aggr function or set analysis - help??

Wed, 06 Oct 2010 20:19:59 GMT

The expression looks pretty good to me. One thing I would try is reversing the conditionals. I don't know if it matters at all, but I always do the field = to the expression. So:

COUNT(IF(ORDER_DATE=aggr(min(ORDER_DATE),Customer_ID),Customer_ID))

Are you getting an error or just an incorrect result? Are you getting any value or is it null for everything?

Duplicates - Need an aggr function or set analysis - help??

Wed, 06 Oct 2010 20:29:16 GMT

To achieve what you want I usualy create a flag in the order table to identify the first purchase, then in the chart you can filter using the flag.

How to create the flag ?

Try something like this

LEFT JOIN (OrderTable)
LOAD Id, Min(Date) as FirstDate
RESIDENT OrderTable
Group By Id ;

OrderTable2:
LOAD * , If(Date=FirstDate ,1 , 0) as Flag
Resident OrderTable;
Drop Table OrderTable;

Maybe there is a problem if a customer orders twice the same date.

Regards

JJJ

Duplicates - Need an aggr function or set analysis - help??

Tue, 12 Oct 2010 13:54:44 GMT

Hi all,

Thanks for your help - sorry for the delay in answering - long weekend here in Canada. 🙂

JJJ- This will not work as this needs to be dynamically calculated base on the users filter selections, so I need to do it in the back end of the chart.

NMiller- Getting an incorrect result would be the best way to put it. It seems to be working in some cases, but not in others. I tried putting it in a table just to see what was going on with the following statement (took out the count for testing purposes):

IF(ORDER_DATE=aggr(min(ORDER_DATE),Customer_ID),Customer_ID,'n/a'))

The dimensions I have in this table are Customer_ID and ORDER_DATE - so what I would expect to see is for a customer with multiple orders is a couple of rows with n/a and only one row with the customer id when the date is the minimum. I am getting this in some cases but in others I get all n/a's.....I can't seem to figure out why the IF statement works for some but not others.... 😞

Thanks,

Duplicates - Need an aggr function or set analysis - help??

Tue, 12 Oct 2010 16:01:15 GMT

Try placing just the Aggr() function in an expression and see if it is returning results for every Customer_ID. I believe only one row per Customer ID would be populated.

You may need a TOTAL modifier in your Min() function. Try:

IF(ORDER_DATE=aggr(min(TOTAL ORDER_DATE),Customer_ID),Customer_ID,'n/a'))

It could be that the Aggr() is still calculating within the context of the dimensions. The TOTAL modifier should get past that. I'm not sure that is the problem though.

Duplicates - Need an aggr function or set analysis - help??

Tue, 12 Oct 2010 17:10:09 GMT

Yes I think I am doing something wrong in the Aggr() function. I want my data to be something like follows:

Customer ID	OrderID	OrderDate	aggr(min(OrderDate),CustomerID)	IF(OrderDate=aggr(min(OrderDate),CustomerID), 'first order','n/a')
1	1	5/1/2010	5/1/2010	first order
1	2	5/2/2010	5/1/2010	n/a
1	3	5/3/2010	5/1/2010	n/a

But what I am actually getting is:

Customer ID	OrderID	OrderDate	aggr(min(OrderDate),CustomerID)	IF(OrderDate=aggr(min(OrderDate),CustomerID), 'first order','n/a')
1	1	5/1/2010	-	n/a
1	2	5/2/2010	5/1/2010	n/a
1	3	5/3/2010	-	n/a

I tried adding the TOTAL qualifier to the min expression but that doesn't seem to make a difference.

Duplicates - Need an aggr function or set analysis - help??

Tue, 12 Oct 2010 17:18:13 GMT

When I put this in a sample app, I got the Aggr result as a number, not a date. On your chart, it looks like a date, but I'm bot sure if that is formatted on the chart that way. It also looks like our expressions had an extra closed parenthesis on the end. Removing that got the result you wanted (with the limited data set I tested). The TOTAL is not required.

I've attached the sample.

Duplicates - Need an aggr function or set analysis - help??

Tue, 12 Oct 2010 19:13:18 GMT

Thanks for all of your help!! - I think actually what I needed was the NODISTINCT clause.