Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Esteemed Contributor

Re: Make ranges of number for customers to select

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

6 Replies
rgvavihs
Valued Contributor

Re: Make ranges of number for customers to select

i think interval match function should be used ,

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

awhitfield
Esteemed Contributor

Re: Make ranges of number for customers to select

Hi Dennis,

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

Regards

ady

Not applicable

Re: Make ranges of number for customers to select

Hi Andrew,

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

Could you please paste the code?

awhitfield
Esteemed Contributor

Re: Make ranges of number for customers to select

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

Re: Make ranges of number for customers to select

Thanks.

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

awhitfield
Esteemed Contributor

Re: Make ranges of number for customers to select

Hi Dennis,

the script is simply an example of how to do it

Andy

Community Browser