LOAD cal_CALENDAR_YYYYMMDD as Date,
(ooxml, embedded labels, table is Calendar);
(ooxml, embedded labels, table is [Cust A]);
(ooxml, embedded labels, table is [Cust B]);
LOAD Max(Date) as MaxDate
Where Count = 2;
Count(DISTINCT Customer) as Count
Where Len(Trim(Volume)) > 0
Group By Date;
LET vMaxDate = Peek('MaxDate');
DROP Table TempTable;
Sample (2).qvw 167.0 K
Well then if that is the case and this number can go up and down, then neither of our calculations are probably going to work. The basis of both of our scripts resides on the fact that number of customers are not going to change very often, because if it does, then how do you really know which number is correct... for example
May 2017 10
June 2017 8
July 2017 10
August 2017 8
September 2017 7
Now, which one would you say is the correct Month? It could be August because the number of customer went down by 2 or it could be September because customer went down another one.
My point is, that if we can hard-code something to simplify our script.... we should do it... because Group Bys are resource intensify and I would want to avoid it as much as possible
Thanks for your suggestions, i have tried using Sunny's suggestion and am,ended it slightly to work out the no.of customers instead of hard coding.
but i cant get it working, i have attached a new sample with some more realistic data (the real data also has a branch assigned to the customer, so there are multiple entries for each date).
i can't see what is wrong, could you please help out if you have time?
I've just simplify script a little bit:
count(distinct customer) as Customer_Count
FROM Data.qvd (qvd)
Group by sales_date;
outer join (Data)
load count(Distinct customer) as Customer_Count_total
FROM Data.qvd (qvd);
max(sales_date) as Max_Date
LET vDate = date(peek('Max_Date'));
drop Tables Data, Variable;