Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have got a problem to solve, if you've any idea please write about it, i would be appreciate.
I've a data set like table 1, if the company can not deliver all of order quantity, system will split. And make 2 new orders which have the same main order no. Like the row no 1,2,3. If still can not deliver the rest of the 2.step of no delivered order, again system split the quantity again. It can be go forever like this (: In my data set I saw 10 step split movement.
What i need is in table 2. I need the main order in one raw and the delivered - not delivered in by columns. I thought the join the orders table by itself but i don't know the splitting count of each main order. I think I need to use something like while loop but at the same time it s not a small table, it s like 500 k rows so I need also performing thin. So thanks for now.
table 1:
Order No | Main Order No | Quantity | State |
1 | NULL | 10 | Not Delivered |
2 | 1 | 5 | Delivered |
3 | 1 | 5 | Not Delivered |
4 | 3 | 3 | Delivered |
5 | 3 | 2 | Not Delivered |
6 | 5 | 2 | Delivered |
7 | NULL | 5 | Not Delivered |
8 | 7 | 1 | Delivered |
9 | 7 | 6 | Not Delivered |
table 2:
Main Order No | Delivered Order No 1 | Not Del Order No 1 | Delivered Order No 2 | Not Del Order No 2 | Delivered Order No 3 |
1 | 2 | 3 | 4 | 5 | 6 |
7 | 8 | 9 | NULL | NULL | NULL |
Hi,
Try the below script, it's a bit complex but could achieve exact what you want. You can add Exit Script; after each transform to understand the logic.
I haven't tested with large data set 🙂
Source:
Load * INLINE [
Order, Main,Quantity,State
1,NULL,10,Not Delivered
2,1,5,Delivered
3,1,5,Not Delivered
4,3,3,Delivered
5,3,2,Not Delivered
6,5,2,Delivered
7,NULL,5,Not Delivered
8,7,1,Delivered
9,7,6,Not Delivered];
HierarchyTable:
Hierarchy(Order, Main, Node, ParentName, NodeName, PathName, '\', Depth)
Load Order, Main,Order as Node
Resident Source;
_tmp:
Load Order, Node1 As MainOrder, Depth
Resident HierarchyTable;
Left Join
Load Order, State Resident Source;
Drop Table Source, HierarchyTable;
MainTable:
Load Order, State&' No '&(Depth-1) as FieldName, MainOrder
Resident _tmp;
Drop Table _tmp;
GenericLabel:
Generic Load MainOrder,FieldName, Order Resident MainTable;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
Load Distinct MainOrder Resident MainTable;
Drop Table MainTable;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName;
Drop Field MainOrder;
Rename Field [Not Delivered No 0] to [Main Order No];
This data load script should help you solve the difficult part:
[RawData]:
LOAD
OrderNo,
if(MainOrderNo='NULL',0,MainOrderNo) as MainOrderNo,
Quantity,
State
FROM [lib://Desktop/Color.xlsx] (ooxml, embedded labels, table is Sheet2);
NoConcatenate
[Data]:
Load OrderNo, MainOrderNo, Quantity, State,
if (MainOrderNo > 0 and RealOrderNo = Peek(OrderNo), Peek(RealOrderNo), RealOrderNo) as RealOrderNo;
Load *,
if(MainOrderNo = 0, OrderNo, Peek(OrderNo)) as RealOrderNo
Resident RawData;
drop table RawData;
Hi,
Try the below script, it's a bit complex but could achieve exact what you want. You can add Exit Script; after each transform to understand the logic.
I haven't tested with large data set 🙂
Source:
Load * INLINE [
Order, Main,Quantity,State
1,NULL,10,Not Delivered
2,1,5,Delivered
3,1,5,Not Delivered
4,3,3,Delivered
5,3,2,Not Delivered
6,5,2,Delivered
7,NULL,5,Not Delivered
8,7,1,Delivered
9,7,6,Not Delivered];
HierarchyTable:
Hierarchy(Order, Main, Node, ParentName, NodeName, PathName, '\', Depth)
Load Order, Main,Order as Node
Resident Source;
_tmp:
Load Order, Node1 As MainOrder, Depth
Resident HierarchyTable;
Left Join
Load Order, State Resident Source;
Drop Table Source, HierarchyTable;
MainTable:
Load Order, State&' No '&(Depth-1) as FieldName, MainOrder
Resident _tmp;
Drop Table _tmp;
GenericLabel:
Generic Load MainOrder,FieldName, Order Resident MainTable;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
Load Distinct MainOrder Resident MainTable;
Drop Table MainTable;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName;
Drop Field MainOrder;
Rename Field [Not Delivered No 0] to [Main Order No];
Hi berkarmagan,
May be like this: easy approach
Source:
Load * INLINE [
Order, Main,Quantity,State
1,NULL,10,Not Delivered
2,1,5,Delivered
3,1,5,Not Delivered
4,3,3,Delivered
5,3,2,Not Delivered
6,5,2,Delivered
7,NULL,5,Not Delivered
8,7,1,Delivered
9,7,6,Not Delivered];
NoConcatenate
Fact:
LOAD
Order,
Main,
Quantity,
State,
if(Main='NULL',0,if(State='Delivered' and Previous(Main)='NULL',1,peek(Count)+1)) as Count
Resident Source where State='Delivered'or Main='NULL'
order by Order;
Concatenate
LOAD
Order,
Main,
Quantity,
State,
if(Main='NULL',0,if(State='Not Delivered' and Previous(Main)='NULL',1,peek(Count)+1)) as Count
Resident Source where State='Not Delivered'or Main='NULL'
order by Order;
Drop table Source;
NoConcatenate
Final_Fact:
LOAD
Distinct
*
Resident Fact;
Drop table Fact;
Dim:
Load
Distinct
Order,
if(Main='NULL',Order,peek([Main Order No])) as [Main Order No]
Resident Final_Fact
order by Order;
Br,
KC
Thanks, i did not know that Hierarchy function, i like that (: and it worked perfectly with little changes on code. Thank you..