I have points in a process that I would like to count based on their text values. I have the process points stored as a transactional table, I would like to keep it this way rather than a cross tab, or flagging system in the load.
So I have customer ID's in a list, repeated with each point in a process that a customer has completed, ie:
Customer ID
Process Point
1
Telephone Enquiry
1
Order Complete
1
Order Shipped
2
Web Enquiry
3
Web Enquiry
3
Order Complete
3
Order Shipped
Can I get a count of how many customers have only submitted a Web enquiry, and have no "Order Complete" record? So above would reveal a count of 1.
Similarly, can I count how many web order have shipped, and how many telephone orders have shipped? This is what I have tried, but doesn't seem to be right. Again, there would be one of each of these using example above.
Set analysis is usually used to get results for comparison values or to ignore a selection because it will do an alternate selection on the data.
On second thought I do not think it will help you for your case.
I would rather suggest to add additional fields containing the combined statuses you need for the display and build these in additional load steps. They will not take much space and if you build them into the qvd files no delay in loading the data.
I would personally also not compare against such lengthy status words but use a numeric field. The dual-function will make it easy to have them displayed in the GUI as full names while internally they will be stored as a few bit number.
