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: 
berkarmagan
Partner - Creator
Partner - Creator

Struggle with Data Joins

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 NoMain Order NoQuantityState
1NULL10Not Delivered
215Delivered
315Not Delivered
433Delivered
532Not Delivered
652Delivered
7NULL5Not Delivered
871Delivered
976Not Delivered

 

table 2:

Main Order NoDelivered Order No 1Not Del Order No 1Delivered Order No 2Not Del Order No 2Delivered Order No 3
123456
789NULLNULLNULL
1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

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

 

 

View solution in original post

4 Replies
thi_pham
Creator III
Creator III

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;

 

Quy_Nguyen
Specialist
Specialist

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

 

 

jyothish8807
Master II
Master II

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

Best Regards,
KC
berkarmagan
Partner - Creator
Partner - Creator
Author

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