topic Re: Generate values in QlikView

Generate values

Wed, 06 Jan 2016 18:33:35 GMT

Hello,

Is it possible to create a field that generates values? I have two fields one called first order and another called last order. It is possible to generate the numbers in between so it would look like the below?

First Order	Last Order	Orders
1	5	1
1	5	2
1	5	3
1	5	4
1	5	5

I'm unsure on how it can be done? Does anyone have an idea?

Re: Generate values

sunny_talwar — Wed, 06 Jan 2016 18:57:35 GMT

Try this:

Table:

LOAD [First Order],

[Last Order],

[First Order] + IterNo() - 1 as Order

While [First Order] + IterNo() - 1 <= [Last Order];

LOAD * Inline [

First Order, Last Order

1, 5

];

Re: Generate values

rbecher — Wed, 06 Jan 2016 19:08:54 GMT

Hi Andrew,

I assume you need this on a client level (or other aggregation):

Orders:
LOAD * INLINE [
    ClientID, OrderID
    A, 3
    A, 4
    A, 5
    A, 6
    A, 7
    B, 2
    B, 3
    B, 4
];

JOIN
LOAD ClientID, min(OrderID) as FirstOrderID, max(OrderID) as LastOrderID
Resident Orders
Group By ClientID;
;

- Ralf

Re: Generate values

MarcoWedel — Thu, 07 Jan 2016 00:42:41 GMT

Hi,

if you don't want to change the row number of your initial table you could create separate order (maybe with additional order facts?) and link tables:

table1:
LOAD *,
    AutoNumberHash128([First Order], [Last Order]) as %Key
INLINE [
    First Order, Last Order
    1, 5
    2, 4
    3, 8
    4, 10
];

tabOrders:
LOAD IterNo() as Orders,
    Ceil(Rand()*100) as someOrderFact1,
    Ceil(Rand()*100) as someOrderFact2,
    Ceil(Rand()*100) as someOrderFact3
While IterNo()<=MaxOrder;
LOAD Max([Last Order]) as MaxOrder
Resident table1;

tabLink:
IntervalMatch(Orders)
LOAD Distinct
    [First Order], [Last Order]
Resident table1;

Join (tabLink)
LOAD Distinct
    [First Order], [Last Order],
    AutoNumberHash128([First Order], [Last Order]) as %Key
Resident tabLink;

DROP Fields [First Order], [Last Order] From tabLink;

hope this helps

regards

Marco