topic Re: Mapping data in App Development

Mapping data

qliksense1thomascook — Fri, 16 May 2025 16:17:16 GMT

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

Re: Mapping data

rwunderlich — Fri, 16 May 2025 17:11:20 GMT

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

Re: Mapping data

joao_renato — Fri, 16 May 2025 17:19:56 GMT

É 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;

Re: Mapping data

qliksense1thomascook — Mon, 19 May 2025 06:08:10 GMT

This is working for me. Thank you very much 😊