How to make G16 display as G16-AO ?

[paulyeo11]

Hi All

i Have below load script work fine for display GROUP_TYPE = 16 TO G16 :-

MapGroupType:
Mapping LOAD
[inv],
[G_TYPE_]
FROM MapGroupTypeTDS.xlsx (ooxml,embedded labels, table is [TDSS]);

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
@307:314T AS GROUP_TYPE_,
// Due to some of invoice enter the wrong GROUP_TYPE
'G'&ApplyMap('MapGroupType',@43:48T,@307:314T) AS GROUP_TYPE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

May i know how to edit the above script , so that it will make G16 display G16-AO ?

May i know how to do it with out using partial reload ?

Paul Yeo

[Vegar]

What about updating your source excel used in the mapping such that where [G_TYPE_] todays says '16' you write '16-AO'

[Anil_Babu_Samineni]

If i get correct input from you? Perhaps this way

SubField('G'&ApplyMap('MapGroupType',@43:48T,@307:314T),'-', 1) AS GROUP_TYPE,

Or

'G'&ApplyMap('MapGroupType',@43:48T,@307:314T)&'-AO' AS GROUP_TYPE,

[paulyeo11]

Hi Sir

The Mapping is for recode some invoice number to 16. As user wrongly enter to 2. Therefore I am not able to enter 16-AO

Any way thank you for your sharing. So I have no choice , I use partial reload to solve it.

Paul Yeo

[paulyeo11]

Hi Sir

My field have 1 to 16 . If I follow your instruction, then all Group will have same name.

Any way I manage to solve it using partial reload.

[Anil_Babu_Samineni]

Fantastic !! Please mark your self as correct answer and post exact script you have written so that It helps for others ..

[paulyeo11 · Accepted solution]

Hi All

Below is the load script using partial reload working fine :-

MapGroupType:
Mapping LOAD
[inv],
[G_TYPE_]
FROM MapGroupTypeTDS.xlsx (ooxml,embedded labels, table is [TDSS]);

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
//@307:314T AS GROUP_TYPE,
// Due to some of invoice enter the wrong GROUP_TYPE
'G'&ApplyMap('MapGroupType',@43:48T,@307:314T) AS GROUP_TYPE_,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// may i know why i do not adding ADD before LOAD GROUP_TYPE_ , SOURCE , yet it still working ?

sales_G:

LOAD GROUP_TYPE_ , SOURCE ,

if (GROUP_TYPE_='G1','G01-GT',
if (GROUP_TYPE_='G2','G02-AP',
if (GROUP_TYPE_='G3','G03-SL',
if (GROUP_TYPE_='G4','G04-CL',
if (GROUP_TYPE_='G5','G05-RW',
if (GROUP_TYPE_='G6','G06-PG',
if (GROUP_TYPE_='G7','G07-BP',
if (GROUP_TYPE_='G8','G08-KL',
if (GROUP_TYPE_='G9','G09-TH',
if (GROUP_TYPE_='G10','G10',
if (GROUP_TYPE_='G12','G12-EX',
if (GROUP_TYPE_='G13','G13-ID',
if (GROUP_TYPE_='G14','G12-EX',
if (GROUP_TYPE_='G16','G16-AO',
)))))))))))))) as [GROUP_TYPE]


resident sales;