Add field during load

[gidon500]

Hi guys

I have a problem to solve  !!

enclosed attached excel file trans

first column is name

second is new trans if y it is new

third id transnum

I need to add the forth column GroupNum ,

a group gets GroupNum , where newtran='y ' , GroupNum=TranNum

all lines that follow this line until the next 'y' with same Name ( not includes ) 

All Lines get the GroupNum from the line which  newtran = 'y'

the enclosed file expalin it better .

thanks

gidon

[#2 · Accepted solution]

Hi Gidon ,

Table1 :

load Rowno() as r ,

Name ,

Newtran,

Trannum ,

Trannum as Groupnum

from tran.xlsx

;

Tran :

load r,

Name ,

Newtran,

Trannum ,

if(Newtran = 'y' , Groupnum, peek(Groupnum) ) as Groupnum

resident  Table1 order by r ;

hope it helps..

Regards,

koti

[gidon500]

Hi Kotty

thanks for your prompt response

i made it work correctly

gidon