Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Make ranges of number for customers to select

 

Hi

We are loading data via an ODBC-connection from a SQL-database. In the short script-example below, we loaded the duration of actions with a StartDate greater then 2014-12-27 23:59:59. The Name/action column is a number.


LOAD Name as action,
StartDate,
Year(StartDate) as Year,
Month(StartDate) as Month,
Week(StartDate) as Week,
Day(StartDate) as Day,

Duration;
SQL SELECT Name,
  StartDate,

  Duration
FROM "Database".dbo.ActionsCustomer WHERE (StartDate > CONVERT(DATETIME, '2014-12-27 23:59:59'));

 

A customer has a repeating range of action-numbers per week. For instance:

Customer 1 has the action-numbers range 100-1400 per week

Customer 2 has the action-number range 1400-3000 per week

 

We have about 20 customers.

How can we attach the ranges to the customers so we can select the sum of the duration of actions per week per customer?

 

I tried SQL Create Table, but now success.

1 Solution

Accepted Solutions
awhitfield
Partner - Champion
Partner - Champion

No Problem!

INTERVALS:

LOAD * Inline
[
FROM_ORDER, TO_ORDER, Range
100, 1400,100>1400
1401, 3000,1401>3000
3001, 4000,3001>4000
]
;

EVENTS:
Load * Inline
[
Customer, Orders
CustA, 100
CustB, 500
CustC, 1500
CustD, 3000
CustE, 3001
CustF, 4000
]
;



IntervalMatch:
IntervalMatch (Orders)
Load Distinct FROM_ORDER, TO_ORDER resident INTERVALS;

Andy

View solution in original post

6 Replies
Anonymous
Not applicable
Author

i think interval match function should be used ,

please provide some sample data so that we can work on it .

awhitfield
Partner - Champion
Partner - Champion

Hi Dennis,

you need to use intervalmatch, a simple example is attached.

Regards

ady

Not applicable
Author

Hi Andrew,

I still use the Qlikview Personal for testing, so I can't open the example

Could you please paste the code?

awhitfield
Partner - Champion
Partner - Champion

No Problem!

INTERVALS:

LOAD * Inline
[
FROM_ORDER, TO_ORDER, Range
100, 1400,100>1400
1401, 3000,1401>3000
3001, 4000,3001>4000
]
;

EVENTS:
Load * Inline
[
Customer, Orders
CustA, 100
CustB, 500
CustC, 1500
CustD, 3000
CustE, 3001
CustF, 4000
]
;



IntervalMatch:
IntervalMatch (Orders)
Load Distinct FROM_ORDER, TO_ORDER resident INTERVALS;

Andy

Not applicable
Author

Thanks.

Do I place this after my own script, or in it?

awhitfield
Partner - Champion
Partner - Champion

Hi Dennis,

the script is simply an example of how to do it

Andy