Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
i think interval match function should be used ,
please provide some sample data so that we can work on it .
Hi Dennis,
you need to use intervalmatch, a simple example is attached.
Regards
ady
Hi Andrew,
I still use the Qlikview Personal for testing, so I can't open the example
Could you please paste the code?
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
Thanks.
Do I place this after my own script, or in it?
Hi Dennis,
the script is simply an example of how to do it
Andy