topic Re: Struggle with Data Joins in App Development

Struggle with Data Joins

berkarmagan — Sat, 16 Nov 2024 19:51:07 GMT

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

Re: Struggle with Data Joins

thi_pham — Tue, 22 Oct 2019 03:17:39 GMT

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;

Re: Struggle with Data Joins

Quy_Nguyen — Tue, 22 Oct 2019 04:57:03 GMT

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];

Re: Struggle with Data Joins

jyothish8807 — Tue, 22 Oct 2019 06:10:01 GMT

Hi berkarmagan,

May be like this: easy approach

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,

Re: Struggle with Data Joins

berkarmagan — Tue, 22 Oct 2019 10:58:19 GMT

Thanks, i did not know that Hierarchy function, i like that (: and it worked perfectly with little changes on code. Thank you..