How to combine 2 field COY_A AND COY_B Inot COY_C ?

[paulyeo11]

Hi All

Below load script working fine :-

GL_TABLE:
LOAD
'COY_A' as SOURCE,
@124:129T as [Reporting Code],
If(@124:129T>=1 and @124:129T<=1,'YES') as [COY_A_L]
FROM C:\Users\Pauly\Dropbox\5 QV_Final\QV_RAW\FS_TDS_.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'COY_B' as SOURCE,
[GL Code] as GL_CODE_,
If([GL Code]>=5000001 and [GL Code]<=5999999,'YES') as [COY_B_L]
FROM
[C:\Users\pauly\Dropbox\5 QV_Final\QV_RAW\GL_PMC_2020.csv](txt, codepage is 1252, embedded labels, delimiter is ',', msq);

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

If(SOURCE=COY_A,(@124:129T>=1 and @124:129T<=1,'YES')) 
If(SOURCE=COY_B,([GL Code]>=5000001 and [GL Code]<=5999999,'YES') as [COY_C]

resident GL_TABLE;

After I try add the load script above ( Red Color ) i get error ,  Can some one advise me.

Paul Yeo

[sunny_talwar]

Try this

If(SOURCE = 'COY_A', If(@124:129T >= 1 and @124:129T <= 1, 'YES'), 
If(SOURCE = 'COY_B', If([GL Code] >= 5000001 and [GL Code] <= 5999999, 'YES'))) as [COY_C]

[paulyeo11]

Dear Sunny

Apprecaite very much for your help.

I get error msg below :-

Field not found - <@124:129T>
TABLE_P:

I add @124:129T to below code :-

load GL_CODE_,[Reporting Code],@124:129T,

I still get error.

Enclosed the file for your ref. kindly create C;\TEMP for test pls.

Paul

[Brett_Bleess]

Paul,  you renamed that field to an alias of Reporting Code up in the first table...  I believe the follow adjustment to Sunny's code should work in that case:

If(SOURCE = 'COY_A', If(@124:129T >= 1 and @124:129T <= 1, 'YES'), 
If(SOURCE = 'COY_B', If([GL Code] >= 5000001 and [GL Code] <= 5999999, 'YES'))) as [COY_C]

 Fudge, cannot edit that! :-), but if you replace the @124:129T value with [Reporting Code] in both places, I believe that should fix the field not found error, but I am not certain...

Regards,
Brett

[paulyeo11]

Hi Brett

Thank you for your sharing.  

I will try the approach you share with me.

Paul Yeo