Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Generate values

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 OrderLast OrderOrders
151
152
153
154
155

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

Tags (1)
3 Replies

Re: Generate values

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

];


Capture.PNG

MVP
MVP

Re: Generate values

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

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:

QlikCommunity_Thread_198623_Pic1.JPG

QlikCommunity_Thread_198623_Pic2.JPG

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

Community Browser