read record below without consider/reading all records <> 1

[lukeert19]

Hello to all,

i try to explain what I have to do with steps ( I think it's more easy to understand better)

1. load Temp table from QVD file
2. count all distict values in a specific column [SerialL3]
3. remove all value <> 1
4. read one value in the next record with peek function and create a new column with this value.

at this moment I have to do that [I have removed some columns for reduce te code]:

Temp table [invoice] --> 100K records

invoice:
load distinct
		[technical_Unit_Id]
      ,RowNo() as C_Row
      ,[Id]
      ,[RMA_ID]
      ,[GlobalNameCode]
      ,[OwnerName]
      ,[RMA_Owner_ID]
      ,[Brand]
      ,[Rma status]
      ,date(floor(RMA_CreationDate)) as RMA_CreationDate
      ,[RMA_CreationBy]
      ,[SerialL3]
      ,RowNo() as N_Row
from qvdTable.qvd
where date([Shipping_date])<=MonthEnd(today()) and date([Shipping_date])>=MonthStart(today(),-4);

after that I have created one other table where I count all distinct value from invoice temp table  without consider GDR = 1  --> 2k records

InvoiceTemp2:
NoConcatenate load *
resident invoice
Order By SerialL3 desc, Shipping_date desc;


GDR:
load *
where GDR <> '1' and GDR <>'-' and not IsNull(GDR);
load
[SerialL3]
,Count(distinct Month(Shipping_date)) as GDR
resident invoice
Group by SerialL3;

then I have to read  the next record  of  specific column (Shipping_date) in GDR table, but I can't find a solution for to do that

FinalInvoice:
load *
 	 ,Peek([Shipping_date], num#(N_Row),'GDR') as Nxt_ShippingDate
resident Invoice
Order By C_Row;

drop Table invoice;
drop Table InvoiceTemp2;

who can help me to solve this my issue?

Luca

[Saravanan_Desingh · Accepted solution]

You can try the last step like this - Desc Order.

FinalInvoice:
load *
 	 ,Peek([Shipping_date]) as Nxt_ShippingDate
resident Invoice
Order By C_Row Desc;

drop Table invoice;
drop Table InvoiceTemp2;