How to substring value from field value mapping sheet

[Anonymous]

Hello all ,

I am trying to find solution for the above mentioned in tittle,

ex:1) AMD/BOM-DLR-HNK/FRA/CHN (International)

     .2) BOM-CHN (Domestic)

Here are the two field value I need to  find wheather its International or Domestic

if there is single international value in whole string then its International else its Domestic

note :- BOM(Bombay)

I have also attached all the international  sectors in excel and the raw QVD of sectors.

Kindly help me with a solution

Thanks & Regards

Santhana Kumar

[ali_hijazi]

in the script you can use wildmatch function

if(wildmatch(sector,'*AMD*','*BBI*','*BHO*'),'International', 'Domestic') as sector_flag

[Peter_Cammaert]

If you have a list of all the airport call signs (both domestic and international), then you can load them into a Mapping table with a special translation value for all international airports (for example, a '#' character) and the original call sign for all domestic airports. Using function MapSubString, you can then translate ALL call signs in a single string into a new string  A single international airport will introduce at least one '#' into the string, and the Index() or SubStringCount() functions can then be used to tell you whether there are 0 or more international airports in your field value.

Use this code as an example of what I mean:

MapCS2Type:

MAPPING

LOAD * INLINE [

CallSign, Type

AMD, #

BOM, BOM

DLR, #

HNK, #

FRA, #

CHN, CHN

];

Table:

LOAD ...

     FlightPath,

     IF (Index(MapSubString('MapCS2Type', FlightPath), '#') > 0,

         'International',

         'Domestic') AS FlightType,

     ...

FROM ... (options);

To better tag all those call signs when loading your mapping table, it might be better to check for domestic ones which is a shorter list. All the others get a '#' marker.

Best,

Peter

[Anonymous]

Dear Peter

Thanks for your reply. I have only International codes in mapping sheet. I need to map them to Sector field and check if any international codes are present in the record and mark them as International. Rest are domestic by default.

Regards

Chintan

[Peter_Cammaert]

No problem. Only load those international codes with a '#' tag in your Mapping table, and it will still work.

BTW your Excel contains only three values AMD, BBI and BHQ. Does that mean that sectors like FRA or HKG are not international?

[Peter_Cammaert · Accepted solution]

If it's just AMD, BBI and BHO (always those three and nothing more. They're all in India, just like BOM) you want to detect, then Ali Hijazi's solution will be perfect.

[Peter_Cammaert]

This is what I get using the technique described above. I did eliminate the 26 NULL values in field SECTOR.

[Anonymous]

Thanks Peter , we are able to get the desired output using your share scenario.

Appreciate your quick help