topic Re: How to substring value from field value mapping sheet in QlikView

How to substring value from field value mapping sheet

Anonymous — Thu, 01 Feb 2018 10:56:04 GMT

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

Re: How to substring value from field value mapping sheet

ali_hijazi — Thu, 01 Feb 2018 11:10:41 GMT

in the script you can use wildmatch function

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

Re: How to substring value from field value mapping sheet

Peter_Cammaert — Thu, 01 Feb 2018 11:47:50 GMT

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

Re: How to substring value from field value mapping sheet

Anonymous — Thu, 01 Feb 2018 12:49:09 GMT

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

Re: How to substring value from field value mapping sheet

Peter_Cammaert — Thu, 01 Feb 2018 12:56:55 GMT

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?

Re: How to substring value from field value mapping sheet

Peter_Cammaert — Thu, 01 Feb 2018 13:06:18 GMT

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.

Re: How to substring value from field value mapping sheet

Peter_Cammaert — Thu, 01 Feb 2018 13:19:59 GMT

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

Re: How to substring value from field value mapping sheet

Anonymous — Fri, 02 Feb 2018 07:41:29 GMT

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

Appreciate your quick help