Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi friends,
I have set of customer transaction data, where i am putting calculated dimension to figure out how many customers fall different price bands eg : for Jan 2016
Band Customer
100-500 3
500-600 1
>600 -
'Not Billed' 1
How to identify not billed ? These are my customers who have not purchased in the selected transaction month ?
Below sample data
Customer Master | |
CustomerID | CustName |
1 | Cust1 |
2 | Cust2 |
3 | Cust3 |
4 | Cust4 |
5 | Cust5 |
Transaction | |||
TranID | TranDate | CustID | TranValue |
1 | 1-Jan-16 | 1 | 100 |
2 | 1-Jan-16 | 2 | 300 |
3 | 1-Jan-16 | 3 | 500 |
4 | 1-Jan-16 | 4 | 100 |
5 | 10-Jan-16 | 1 | 500 |
6 | 2-Feb-16 | 1 | 100 |
7 | 21-Feb-16 | 1 | 600 |
8 | 2-Feb-16 | 2 | 400 |
9 | 2-Mar-16 | 2 | 900 |
10 | 2-Mar-16 | 3 | 800 |
11 | 12-Mar-16 | 4 | 600 |
12 | 1-Mar-16 | 4 | 700 |
13 | 2-Mar-16 | 4 | 100 |
Please find attached file for solution.
Regards,
Jagan.
used IntervalMatch
e.g.
OrderLog:
LOAD * INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];
EventLog:
LOAD * INLINE [
Time, Event, Comment
00:00, 0, Start of shift 1
01:18, 1, Line stop
02:23, 2, Line restart 50%
04:15, 3, Line speed 100%
08:00, 4, Start of shift 2
11:43, 5, End of production
];
IntervalMatch (Time) LOAD Start, End Resident OrderLog;
The following table box can now be created in QlikView:
Tablebox | |||||
Time | Event | Comment | Order | Start | End |
00:00 | 0 | Start of shift 1 | - | - | - |
01:18 | 1 | Line stop | A | 01:00 | 03:35 |
02:23 | 2 | Line restart 50% | A | 01:00 | 03:35 |
04:15 | 3 | Line speed 100% | B | 02:30 | 07:58 |
04:15 | 3 | Line speed 100% | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | D | 07:23 | 11:43 |
11:43 | 5 | End of production | D | 07:23 | 11:43 |
See attached qvw
Thanks Gysbert Wassenaar. It is working.