JOINING TABLES THROUGH MULTIPLE FIELDS

[Anonymous]

i don't even know if the title of my question fits...

i have two tables like these:

Flight N°	delay1 code	delay1 time	delay2 code	delay2 time	delay3 code	delay3 time	TOT delay time
1	AA	0:15	FH	1:40	MK	0:05	2:00
2	AG
3	FH
4	LL
5	MK

delay code	delay description
AA	late arrival
AG	stand busy
FH	aircraft damage
LL	heavy weather
MK	take-off permission denied

so i hav three types of delay (3 possible causes of delay) but only one association for the delay type and its description. of course delay code fields can all be filled as filled in part; the codes that can be used in a field are the ones unused in the precedent field(s). how can i link these two tables? thanx, good halloween

[Gysbert_Wassenaar]

Try using a mapping table and applymap:

MapDelay:

MAPPING LOAD * INLINE [

    delay code, delay description

    AA, late arrival

    AG, stand busy

    FH, aircraft damage

    LL, heavy weather

    MK, take-off permission denied

];

Table1:

LOAD

"Flight N°",

"delay1 code",

ApplyMap('MapDelay',[delay1 code]) as "delay1 desc",

"delay1 time",

"delay2 code",

ApplyMap('MapDelay',[delay2 code]) as "delay2 desc",

"delay2 time",

"delay3 code",

ApplyMap('MapDelay',[delay3 code]) as "delay3 desc",

"delay3 time",

TOT delay time"

.... ;

[Anonymous]

i've tried... i don't get any syntax error but it doesn't load any coloumn from the AppMap function as written above...

[whiteline]

Hi.

Why don't you just load your data in another way ?

I think it could solve most of your problems.

For example, instead of having dozens of fields 'delayN code' you could have just 4 fields in first table:

Fligh №, dalay code, delay time, delay number.

[Anonymous]

i don't load my data in other ways because i can't.

[whiteline]

Data:

LOAD

     "Flight N°",

     "delay1 code" as "delay code",

     "delay1 time" as "delay time",

     1 as "delay number"

Resident Source;

Concatenate(Data)

LOAD

     "Flight N°",

     "delay2 code" as "delay code",

     "delay2 time" as "delay time",

     2 as "delay number"

Resident Source;

Concatenate(Data)

LOAD

     "Flight N°",

     "delay3 code" as "delay code",

     "delay3 time" as "delay time",

     3 as "delay number"

Resident Source;

and so on...

[Anonymous]

i didn't mean "i can't because i'm not able to but i would like", i meant "i can't because my data must mantain the format i specified above first"

[whiteline]

Why ?

With the format above you could easily create tables in forms that you've posted.

Anyway, if you want to use your structure, load your  table with descriptions n-times for each delay-n code.