Qlik help

[Bharathi09]

Hi all,

I have 3 tables

Cost Master:

Origin	Destination	Cost
Chennai	Coimbatore	$10.00
Coimbatore	Pune	$15.00
Chennai	Pune	$20.00
Coimbatore	Tambaram	$25.00
Pune	Tambaram	$30.00

Movements:

Origin	Destination
Chennai	Coimbatore
Coimbatore	Pune
Chennai	Pune
Coimbatore	Chennai
Pune	Chennai
Hyderabad	Chennai

Mapping Table:

Location	Alternate Location
Chennai	Tambaram
Coimbatore	Erode
Pune	Thane
Hyderabad	Secunderabad

I need to build a result in such a way that I need to map the cost from Cost Master to Movements. But I need to consider alternate locations as well. For eg., if I don't have value for Cost between Coimbatore to Chennai, I need to use Alternate location for one of them like Coimbatore to Tambaram. 

The below is the result table:

Result Table
Origin	Destination	Cost	Cost Origin	Cost Destination
Chennai	Coimbatore	$10.00	Chennai	Coimbatore
Coimbatore	Pune	$15.00	Coimbatore	Pune
Chennai	Pune	$20.00	Chennai	Pune
Coimbatore	Chennai	$25.00	Coimbatore	Tambaram
Pune	Chennai	$30.00	Pune	Tambaram
Hyderabad	Chennai	No Data	Hyderabad	Chennai

Please help

Thanks in advance 

[G3S]

hi,

Kinda got there..😅 not the full table. 

Result:

Script:

MappingTable:
Mapping LOAD
*inline
[
Destination, AlternateLocation
Chennai, Tambaram
Coimbatore, Erode
Pune, Thane
Hyderabad, Secunderabad
];

Movements:
LOAD *,
ApplyMap('MappingTable', Destination,'n/a') As AlternateDestination

inline
[
Origin, Destination
Chennai, Coimbatore
Coimbatore, Pune
Chennai, Pune
Coimbatore, Chennai
Pune, Chennai
Hyderabad, Chennai

];

CostMaster:
join (Movements)
LOAD * inline
[
Origin, Destination, Cost, OriginDestination
Chennai, Coimbatore, 10, ChennaiCoimbatore
Coimbatore, Pune, 15, CoimbatorePune
Chennai, Pune, 20, ChennaiPune
Coimbatore, Tambaram, 25, CoimbatoreTambaram
Pune, Tambaram, 30, PuneTambaram
]
;

------

 worked it out in Excel though.. if it is of any help for steps..

[vinieme12]

As below

Costs:
load * inline [
Origin,Destination,Cost
Chennai,Coimbatore,$10.00
Coimbatore,Pune,$15.00
Chennai,Pune,$20.00
Coimbatore,Tambaram,$25.00
Pune,Tambaram,$30.00
];

left Join(Costs)
Alt_Location:
load Location as AltDestination,AlternateLocation as Destination,'Alt' as locationType inline [
Location,AlternateLocation
Chennai,Tambaram
Coimbatore,Erode
Pune,Thane
Hyderabad,Secunderabad
];

MappingTable:
Load
Origin&'-'&Destination as key, Cost , Destination
Resident Costs;
Concatenate(MappingTable)
Load Origin&'-'&AltDestination as key,Cost, Destination
Resident Costs
Where len(locationType)>0;

MapCost:
Mapping Load key,Cost
Resident MappingTable;

MapDest:
Mapping Load key,Destination
Resident MappingTable;
drop tables MappingTable,Costs;

Movements:
load *
,Applymap('MapCost',Origin&'-'&Destination,'No Data') as Cost
,Origin as CostOrigin
,Applymap('MapDest',Origin&'-'&Destination,Destination) as CostDestination
inline [
Origin,Destination
Chennai,Coimbatore
Coimbatore,Pune
Chennai,Pune
Coimbatore,Chennai
Pune,Chennai
Hyderabad,Chennai
]
;

exit Script;