I want to combine 2 IF load script into 1 , I get the wrong result.

[paulyeo11]

Hi All

I have below script  , i have issue on the script mark with red color  , it return wrong value :-

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
GL_TABLE:
LOAD
'TDS' as SOURCE,
date#(MakeDate(@53:57T,if(@50:52T=13,12,@50:52T) ,1),'DD/MM/YYYY')as date,
@124:129T as [Reporting Code],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\FS_TDS_.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
[Account Code] as AccountCode,
[Deb./Cred. (LC)] AS Amount,
Date( Date#([Posting Date], 'DD.MM.YY'), 'DD/MM/YYYY') as [date]
FROM
[C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\GL_PMC_2019.xlsx]
(ooxml, embedded labels, table is [Modified GL Data]);

TABLE_P:
Add load [AccountCode],
If([Reporting Code]>=1 and [Reporting Code]<=15,'P_Revenue') as [P_Revenue],
if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue1') as [P_Revenue1],
// When i try to combine above 2 script into 1 , Below script now working
If([Reporting Code]>=1 and [Reporting Code]<=15,'P_Revenue2') or

if([AccountCode]>=5000000 and [AccountCode]<=5999999,'P_Revenue2') as [P_Revenue2]

resident GL_TABLE;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Hope some one can advise me where go wrong ?

Paul

[Vegar]

If([Reporting Code]>=1 and [Reporting Code]<=15,

   'P_Revenue2',

   if([AccountCode]>=5000000 and [AccountCode]<=5999999,

      'P_Revenue2',

      Null() )

) as [P_Revenue2]

[JGMDataAnalysis]

If(([Reporting Code] >= 1 And [Reporting Code] <= 15) Or
      ([AccountCode] >= 5000000 And [AccountCode] <= 5999999), 'P_Revenue2'

) AS [P_Revenue2]

[paulyeo11]

Hi Sir

Thank you very much for your advise.
I try to apply your expression into my table it is not work.

I try to add [Reporting Code], it also not work.

TABLE_P:
load [AccountCode],[Reporting Code],

If([Reporting Code]>=1 and [Reporting Code]<=15,

'P_Revenue_BOTH',

if([AccountCode]>=5000000 and [AccountCode]<=5999999,

'P_Revenue_BOTH',

Null() )

) as [P_Revenue_BOTH]
resident GL_TABLE;

When i change to below script  , it work on SOURCE =TDS but not PMC  . This mean you expression is working , except i don't know how to to add [AccountCode] to Partial reload section. 

TABLE_P:
load [Reporting Code] ,

If([Reporting Code]>=1 and [Reporting Code]<=15,

'P_Revenue_BOTH',

if([AccountCode]>=5000000 and [AccountCode]<=5999999,

'P_Revenue_BOTH',

Null() )

) as [P_Revenue_BOTH]
resident GL_TABLE;

I have found out the below :-

If using below , Both TDS & PMC return zero value :-

load [Reporting Code] ,[AccountCode],

If using below , Only PMC return correct value , TDS return zero value :-

load [AccountCode],

If using below , Only TDS return correct value , PMC return zero value :-

load [Reporting Code] ,

So the problem is cause by below :-

load [Reporting Code] ,[AccountCode],

But i don't know how to correct it.Paul

[paulyeo11]

Hi JG

Thank you very much for your very short expression It work fine. Now my issue is during Partial reload , i am not allow to load 2 field :-

load [Reporting Code] ,[AccountCode],

I can only load one field :-

load [AccountCode],

or

load [Reporting Code] ,

You have any idea ?

Paul

[paulyeo11]

Hi All

I have found a solution, what i did is i recode the AccountCode to [Reporting Code].

So for both SOURCE TDS and PMC both using one common field.

it work fine.

Thank you very much.

Paul