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

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?

3 Replies
sunny_talwar

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
MarcoWedel

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