Why i can not add Concatenate in between FOR ?

[paulyeo11]

Hi All

Below script work fine :-

SET vDevelopment = 0; //server = 0 // =2 for PY NEW NB // 1 FOR PY OLD NB
IF $(vDevelopment) = 0 THEN
SET vRAWPath = 'C:\Users\pauly\Dropbox\QV_RAW_ISDN\'; //server folder
SET vFile200 = 'GL_PM.CSV';
SET vFile261 = 'FS_TDS_ADL.TXT';
else
END IF

For Each i in '$(vFile200)'
LOAD
'$(i)' as SOURCE_,
[GL Code],
[Customer/Vendor Name]
FROM
$(vRAWPath)$(i)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i;

// SECTION (A) Concatenate

For Each i in '$(vFile261)'
LOAD
'$(i)' as SOURCE_,
ApplyMap('MAP_TDS_OTH_INCOME',[@1:16T],'OTH_INCOME') as [oTH_INCOME],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount] // This will join the sign and AMT and create new var = AMOUNT.
FROM
$(vRAWPath)$(i)
(ansi, fix, no labels, header is 0, record is line);
NEXT i;

When i add Concatenate at section (A)

i get error msg below :-

Unknown statement
Concatenate

Can some one advise me where go wrong ?

Paul

[chrismarlow · Accepted solution]

Hi,

Where I have done this in the past I have similar to;

For Each i in '$(vFile261)'
Concatenate (tablename)
LOAD

Where I have explicitly named my table in the previous load (I am assuming you are trying to concatenate the File200's with the File261s).

I've seen quite a few examples where for the first load in the 'For' the explicit naming of the table is done, then for the remaining tables the concatenate.

Cheers,

Chris. 

[Saravanan_Desingh]

Can you try this one? Just added a Table name (tabOut).

SET vDevelopment = 0; //server = 0 // =2 for PY NEW NB // 1 FOR PY OLD NB
IF $(vDevelopment) = 0 THEN
SET vRAWPath = 'C:\Users\pauly\Dropbox\QV_RAW_ISDN\'; //server folder
SET vFile200 = 'GL_PM.CSV';
SET vFile261 = 'FS_TDS_ADL.TXT';
else
END IF

For Each i in '$(vFile200)'
tabOut:
LOAD
'$(i)' as SOURCE_,
[GL Code],
[Customer/Vendor Name]
FROM
$(vRAWPath)$(i)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i;

// SECTION (A) Concatenate

For Each i in '$(vFile261)'
tabOut:
LOAD
'$(i)' as SOURCE_,
ApplyMap('MAP_TDS_OTH_INCOME',[@1:16T],'OTH_INCOME') as [oTH_INCOME],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount] // This will join the sign and AMT and create new var = AMOUNT.
FROM
$(vRAWPath)$(i)
(ansi, fix, no labels, header is 0, record is line);
NEXT i;

[Saravanan_Desingh · Accepted solution]

One more to try, using Concatenate.

SET vDevelopment = 0; //server = 0 // =2 for PY NEW NB // 1 FOR PY OLD NB
IF $(vDevelopment) = 0 THEN
SET vRAWPath = 'C:\Users\pauly\Dropbox\QV_RAW_ISDN\'; //server folder
SET vFile200 = 'GL_PM.CSV';
SET vFile261 = 'FS_TDS_ADL.TXT';
else
END IF

For Each i in '$(vFile200)'
tabOut:
LOAD
'$(i)' as SOURCE_,
[GL Code],
[Customer/Vendor Name]
FROM
$(vRAWPath)$(i)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i;

// SECTION (A) Concatenate

For Each i in '$(vFile261)'
Concatenate (tabOut)
LOAD
'$(i)' as SOURCE_,
ApplyMap('MAP_TDS_OTH_INCOME',[@1:16T],'OTH_INCOME') as [oTH_INCOME],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount] // This will join the sign and AMT and create new var = AMOUNT.
FROM
$(vRAWPath)$(i)
(ansi, fix, no labels, header is 0, record is line);
NEXT i;

[paulyeo11]

Hi Sir

Your mind very flexible.

Thank you.

Paul

[Brett_Bleess]

If one of the two posts Saran left you got you a working solution, we would appreciate it if you would return to the thread and use the Accept as Solution button on the post that you used for the working solution to give them credit for the assistance and let other Members know what worked.  

Thanks,
Brett