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

[kumar2]

Hi Bharat,

Thanks for the reply

if i add some new orders its was working almost all fine but some mismatch in the expected output.Please find the sample data and code

tab1:
LOAD *, If(Peek([Line item])=[High Line Item],Peek([New High Line Item]),[High Line Item]) As [New High Line Item]
;

LOAD * INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,1000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020
];

NoConcatenate
tab2:
LOAD *,
[Order no]&[New High Line Item] as Key
RESIDENT tab1;

Left Join(tab2)

Load [Order no]&[Line item] as Key,
[New High Line Item] AS New
Resident tab1;

Drop table tab1;
NoConcatenate

Load
[Order no],
[Line item],
If(Isnull([New]),[New High Line Item],New) AS [High Line Item]
Resident tab2;

Drop table tab2;

Exit Script;

output of the above code:

for the line items 5020 and 6060 it was showing different it should get 1000 as the expected output if  i see the input

orderno ,line item,higher line item

345678,15,10
345678,20,15
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,1000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020

for the line item 5020-- higher line item is 3060 for the line item 3060--higher line item is 3040 for the line item 3040--higher line item is 2020 for the line item 2020 higher line item is 1010 and for the line item 1010-- higher line item is 1000 and there is no line item for 1000 so it is the highest line item for 5020.

Thanks,

Chetan

[Kushal_Chawda · Accepted solution]

try below

Data:
LOAD * INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,1000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020
];

New:
NoConcatenate
Load *,
     if(Peek([Order no])<>[Order no],[High Line Item],Peek('New High Line Item')) as [New High Line Item]
Resident Data
Order by [Order no],[Line item];

Drop Table Data;

[Saravanan_Desingh]

Hi @kumar2 . My solution seems be not ideal for all conditions. Please check @Kushal_Chawda solution. Its perfect and elegant.

[Saravanan_Desingh]

Ok, this seems to be Hierarchical data. Give me some time. Let me try something. 

[Saravanan_Desingh]

Please check this code:

tab1:
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
];
Concatenate(tab1)
LOAD [Order no], Min([High Line Item]) As [Line item]
Resident tab1
Group By [Order no];

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]

Hi Kush,

Thanks for your reply

But it doesn't meet the required criteria.Please find the below sample data and code which i have used

Data:
LOAD * INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,2000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020
];

New:
NoConcatenate
Load *,
if(Peek([Order no])<>[Order no],[High Line Item],Peek('New High Line Item')) as [New High Line Item]
Resident Data
Order by [Order no],[Line item];

Drop Table Data;

Exit Script;

The output which i am getting is 

if you see the order no 3456789 for the line item 3020 it  is 2000 and there is no line item for 2000 so for the line item 3030 it was the new high line item 2000 but it was showing as 1000 which was isn't

Thanks,

Chetan

[kumar2]

Hi Saran,

Thanks for the code

It was working fine as expected just one last request instead of showing the line item number at the new high line item is it possible to show the high line item number in the sense

if you see the above screen shot for the line item 21 the new high line item is showing as 21 which was the line item number for the order instead of showing line item number is it possible to show the high line item number 11

Thanks,

Chetan

[Kushal_Chawda]

try below

Data:
LOAD *,AutoNumberHash256([Order no]&[Line item]) as Key INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,21,11
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,2000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020
];

Hierarchy1:
HierarchyBelongsTo([Line item],[High Line Item],Key,AncestorID, AncestorName, DepthDiff)
Load [Line item],[High Line Item],Key
Resident Data;

Left Join(Data)
Load  Key ,
      FirstSortedValue(AncestorID,-DepthDiff) as [Line Item Max Depth]
Resident Hierarchy1
group by Key;

Drop Table Hierarchy1;

Left Join(Data)
Load  Key,
     AutoNumberHash256([Order no]&[Line Item Max Depth]) as Key1
Resident Data;

Left Join(Data)
Load  Key as Key1,
     [High Line Item] as [New High Line Item]
Resident Data;

Drop Fields [Line Item Max Depth],Key,Key1;

[Kushal_Chawda · Accepted solution]

check this one as well

Data:
LOAD *, AutoNumberHash256([Order no]&[Line item]&[High Line Item]) as Key INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,21,11
345678,25,20
345678,30,20
345678,35,15
3456789,1010,1000
3456789,2020,1010
3456789,3020,2000
3456789,3040,2020
3456789,3060,3040
3456789,3080,1000
3456789,5020,3060
3456789,6020,1000
3456789,6040,3080
3456789,6060,5020
];

Hierarchy:
Hierarchy([Line item],[High Line Item],Node)
Load [Line item],[High Line Item],[Order no]&'-'&[High Line Item] as Node
Resident Data;

Left Join(Data)
Load AutoNumberHash256(SubField(Node1,'-',1)&[Line item]&[High Line Item]) as Key,
     SubField(Node1,'-',2) as [New High Line Item]
Resident Hierarchy;
Drop table Hierarchy;