topic JOINING TABLES THROUGH MULTIPLE FIELDS in QlikView

JOINING TABLES THROUGH MULTIPLE FIELDS

Anonymous — Wed, 31 Oct 2012 16:08:54 GMT

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

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

Gysbert_Wassenaar — Wed, 31 Oct 2012 17:45:13 GMT

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"

.... ;

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

Anonymous — Mon, 05 Nov 2012 11:19:16 GMT

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

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

whiteline — Mon, 05 Nov 2012 15:16:23 GMT

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.

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

Anonymous — Tue, 06 Nov 2012 08:36:47 GMT

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

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

whiteline — Tue, 06 Nov 2012 15:32:13 GMT

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...

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

Anonymous — Tue, 06 Nov 2012 16:03:50 GMT

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"

Re: JOINING TABLES THROUGH MULTIPLE FIELDS

whiteline — Tue, 06 Nov 2012 17:10:51 GMT

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.