How to write load script at partial reload for [Reporting Code]< 00500 use date1 and more then 00500 use date2 ?

[paulyeo11]

Hi All

My raw data have 2 date , date1 and date2.

Below is my load script :-

LOAD

'TDSS' as SOURCE_GL,

date#(MakeDate(@53:57T, @50:52T ,@58:59T),'DD/MM/YYYY') as date1,

makedate(@53:57T,@50:52T,1) as date2,

@124:129T  as [Reporting Code],

if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]   

FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

For my application ,

when reporting code 00001 - 00500 i should use date1

when reporting code 00501 - 99999 i should use date2

i make use of partial reload to create date3 , My requirement is to create a new feild name = date3 , the condition as below :-

My Partial reload script as below :-

TABLE_P:

left keep (GL_TABLE)

load [Reporting Code],

If([Reporting Code]<=500,date1 and [Reporting Code]>=500,date2) as [date3]

resident GL_TABLE;

There it no error , it does create a new date3. But it is not working ,  Can some one advise me where i go wrong ?

Paul

[paulyeo11]

my QV Doc

[Anonymous]

Use Add before your Load Statement for the partial reload to work .

[settu_periasamy · Accepted solution]

Hi Paul,

May be try this

TABLE_P:
left keep (GL_TABLE)
load [Reporting Code],

//If([Reporting Code]<=500,date1 and [Reporting Code]>=500,date2) as [date3],

If([Reporting Code]<=500,date1,
    if([Reporting Code]>=500,date2)) as [date3],

If([Reporting Code]>=1 and [Reporting Code]<=19,'rEVENUE') as [rEVENUE]

resident GL_TABLE;

[paulyeo11]

Hi Settu

Thank you very much. it work.

Paul