Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
];
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
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