Iteration of the values

[kumar2]

Hi,

I have requirement to do the looping until all the values in the column has changed to first value. Please find the below example sample data

Order no,Line item,High Line Item

345678,15,10

345678,20,15

345678,25,20

345678,30,25

For the second row high line item is 15  its is indirectly point to line item 15 is pointing to high level item 10 and the output for the second line higher line item should be 10 same for the others. I need to implement this logic on the huge data sets where there will be more number of line items

Expected output:

Order no,Line item,High Line Item

345678,15,10

345678,20,10

345678,25,10

345678,30,10

Regards & Thanks,

Chetan

[Saravanan_Desingh · Accepted solution]

My version is ready now.

tab1:
LOAD *, [Order no]&'-'&[Line item] As Key INLINE [
    Order no, Line item, High Line Item
    345678, 15, 10
    345678, 20, 15
    345678, 25, 20
    345678, 30, 21
    345678, 35, 15
    345678, 21, 11    
	3456789, 1010,1000
	3456789, 2020,1010
	3456789, 3020,1000
	3456789, 3040,2020
	3456789, 3060,3041
	3456789, 3080,1000
	3456789, 5020,3060
	3456789, 6020,1000
	3456789, 6040,3080
	3456789, 6060,5020
];
Concatenate(tab1)
LOAD [Order no], ([High Line Item]) As [Line item], [Order no]&'-'&[High Line Item] As Key
Resident tab1
Where Not Exists(Key,[Order no]&'-'&[High Line Item]);

tab2:
Hierarchy([Line item], [High Line Item],Node)
LOAD *, [Line item] As Node
Resident tab1;

tab3:
LOAD [Order no], [Line item], [High Line Item], Node1 As [New High Line Item]
Resident tab2
Where Not IsNull([High Line Item]);

Drop Table tab1, tab2;

[Saravanan_Desingh]

Output.

[kumar2]

Thanks @Saravanan_Desingh  and @Kushal_Chawda  both of your solutions are working awesome  and thanks for your support

[Saravanan_Desingh]

I just realized, my code can still be simplified as below.

tab1:
Hierarchy([Line item], [High Line Item],Node)
LOAD *, [High Line Item] As Node;
LOAD * INLINE [
    Order no, Line item, High Line Item
    345678, 15, 10
    345678, 20, 15
    345678, 25, 20
    345678, 30, 21
    345678, 35, 15
    345678, 21, 11    
	3456789, 1010,1000
	3456789, 2020,1010
	3456789, 3020,1000
	3456789, 3040,2020
	3456789, 3060,3041
	3456789, 3080,1000
	3456789, 5020,3060
	3456789, 6020,1000
	3456789, 6040,3080
	3456789, 6060,5020
];

tab2:
LOAD [Order no], [Line item], [High Line Item], Node1 As [New High Line Item]
Resident tab1;

Drop Table tab1;

[kumar2]

Thanks @Saravanan_Desingh it's works

But the  code is taking large amount of time but I have data around 14,000,000 is any way to reduce the data loading time and achieve this.

Thanks,

Chetan