Mapping data

[qliksense1thomascook]

Hi everyone,

I have three fields Sector from main table, Airport Codes and Type from master table.

Sector has multiple airport codes separated by '-' and I need to map them with Airport Codes from the master table.

1. If even one of the airport code from sector is not available in Airport Codes from master table, it should give NA.

2. If even one airport code from sector matches with Airport Codes from master table and the Type is showing International, it should give International.

3. If all the airport codes from sector matches with Airport Codes from master table and the Type is showing Domestic for all the airport codes then it should give Domestic.

Any help is appreciated. 

Thanks in advance

[rwunderlich]

It all sounds doable in Qlik but a bit challenging to give a specific answer without sample data. Can you provide some sample input and output data?

-Rob

[joao_renato · Accepted solution]

É possível usar algumas combinações de funções para obter o resultado esperado, como por exemplo MapSubstring, Replace, subfield etc.

Fiz um exemplo um pouco mais extenso, mas talvez seja útil para usar como apoio no seu caso.

DimTable:
LOAD * Inline [
AirportCodes , AirportType
GRU , Domestic
GIG , Domestic
CGH , Domestic
SDU , Domestic
CNF , Domestic
JFK , International
CDG , International
EZE , International
];

MapAIrport:
Mapping Load AirportCodes as cod,
AirportType as desc
Resident DimTable;

MainTable:
Load * Inline [
Id , Sector
1 , GRU-JFK
2 , CGH-SDU-CNF
3 , GRU-XXX
4 , CGH-CDG
5 , SDU-ABC
6 , GIG-EZE
];

Tmp:
Load Id, Applymap('MapAIrport', SubField(Sector, '-'), 'NA') as Result
Resident MainTable;

MapNA:
Mapping Load Id,
1 as desc
Resident Tmp where Result = 'NA';

MapInternational:
Mapping Load Id,
1 as desc
Resident Tmp where Result = 'International';

MainTableFinal:
Load Id,
Sector,
If(ApplyMap('MapNA', Id, 0) = 1, 'NA',
If(ApplyMap('MapInternational', Id, 0) = 1, 'International', 'Domestic')) as Type
Resident MainTable;
Drop tables MainTable, Tmp;

[qliksense1thomascook]

This is working for me. Thank you very much 😊