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

[settu_periasamy]

Hi,

is 3rd and 4th records are right?

345678,15,10            -> 345678,15,10

345678,20,15           -> 345678,20,10

345678,25,20          -> 345678,25,10

345678,30,25           -> 345678,30,10

[kumar2]

It's not like 3 &4 the records

It will be in any where like

325678,15,10--->325678,15,10

325678,20,15--->325678,20,10

325678,25,20--->325678,25,10

325678,30,25--->325678,30,10

325678,35,15--->325678,35,10

If you see the fourth record the highest line item is 25 it should go and check the line item 25 what is the highest line item it is 20 and again it should go and check for the line item 20 what is the highest line item that is 15 and again it need to go and check the 15 what is the highest line item that is 10 for the 10 there is no highest line item and 10 is the output for the record 4 like that I need the check for the every record

If you see the fifth record the highest line item is 15 it should go and check the line item 15 what is the highest line item that should display in the output as 10 it was the direct 

Thanks,

Chetan

[Saravanan_Desingh]

Are you looking something like this?

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

[Saravanan_Desingh]

Output.

[kumar2]

Thanks saran for your reply

But it doesn't fit my criteria if I add the extra colomn it was showing the different output 

After applying the formula I was getting this type of result

345678,15,10--->345678,15,10

345678,20,15---->345678,20,10

345678,25,20--->345678,25,10

345678,30,20--->345678,30,20

345678,35,15--->345678,35,15

Expected output:-

345678,15,10--->345678,15,10

345678,20,15---->345678,20,10

345678,25,20--->345678,25,10

345678,30,20--->345678,30,10

345678,35,15--->345678,35,10

Here in the fourth record highest line item 20 is pointing to line item 20 which is the second record and for that highest line item is 15 and highest line item 15 should point to line item 15 and highest line item is 10 so the output should be 10 for fourth record highest line item

Thanks,

Chetan

[Saravanan_Desingh]

Please check this code.

tab1:
LOAD RowNo() As RowID,* INLINE [
Order no,Line item,High Line Item
345678,15,10
345678,20,15
345678,25,20
345678,30,20
345678,35,15
];

tmp1:
LOAD Concat(DISTINCT [High Line Item],',',[High Line Item]) As High
Resident tab1;

Let vHighs=Peek('High');
Trace *** vHighs=$(vHighs);

Drop Table tmp1;

For Each vHigh In $(vHighs)
	Trace *** vHigh=$(vHigh);
	
	tmp:
	LOAD *, If(Peek([Line item])=[High Line Item] Or Peek([High Line Item])=[High Line Item],Peek([New High Line Item]),[High Line Item]) As [New High Line Item]
	Resident tab1
	Where [Line item]=$(vHigh) Or [High Line Item]=$(vHigh)
	Order By [Order no],[High Line Item]
	;
	 	
Next

Left Join(tab1)
LOAD DISTINCT * Resident tmp;

Drop Table tmp;

[Saravanan_Desingh]

Output.

[bharatchauhan]

Hi Chetan,

Please check if this works for you:

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,25
345678,30,20
345678,35,15
];

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;

Regards,
Bharat

[kumar2]

Hi Saran,

I almost all work fine but for new record the output is showing different please find the below code and sample data

tab1:
LOAD RowNo() As RowID,* 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
];

tmp1:
LOAD Concat(DISTINCT [High Line Item],',',[High Line Item]) As High
Resident tab1;

Let vHighs=Peek('High');
Trace *** vHighs=$(vHighs);

Drop Table tmp1;

For Each vHigh In $(vHighs)
Trace *** vHigh=$(vHigh);

tmp:
LOAD *,
If(Peek([Line item])=[High Line Item] Or Peek([High Line Item])=[High Line Item],Peek([New High Line Item]),[High Line Item]) As [New High Line Item]
Resident tab1
Where [Line item]=$(vHigh) Or [High Line Item]=$(vHigh)
Order By [Order no],[High Line Item]
;

Next

Left Join(tab1)
LOAD DISTINCT * Resident tmp;

Drop Table tmp;

output which i am getting is:

for the line items 5020 it's getting multiple vaues (1000,3060) and 6060 it was showing 3060 instead of 1000

Thanks,

CHetan