How to get missing data into count

[manojkulkarni]

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

[jagan]

Please find attached file for solution.

Regards,

Jagan.

[ali_hijazi]

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

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[manojkulkarni]

Thanks Gysbert Wassenaar. It is working.