Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
manojkulkarni
Partner - Specialist II
Partner - Specialist II

How to get missing data into count

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
CustomerIDCustName
1Cust1
2Cust2
3Cust3
4Cust4
5Cust5

   

Transaction
TranIDTranDateCustIDTranValue
11-Jan-161100
21-Jan-162300
31-Jan-163500
41-Jan-164100
510-Jan-161500
62-Feb-161100
721-Feb-161600
82-Feb-162400
92-Mar-162900
102-Mar-163800
1112-Mar-164600
121-Mar-164700
132-Mar-164100
1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

See attached qvw


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
jagan
Partner - Champion III
Partner - Champion III

Please find attached file for solution.

Regards,

Jagan.

ali_hijazi
Partner - Master II
Partner - Master II

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
I can walk on water when it freezes
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

See attached qvw


talk is cheap, supply exceeds demand
manojkulkarni
Partner - Specialist II
Partner - Specialist II
Author

Thanks Gysbert Wassenaar. It is working.