topic Re: Use field from a different table in a table in QlikView

Use field from a different table in a table

Tue, 06 Nov 2012 16:04:05 GMT

Hi,

I have these tables namewith the filed:

I have
table1_Tmp: ........ table1: LOAD %nameComplete, ..., [Date 1], [Date 2], calc(Date) AS %DurationDate RESIDENT table1_Tmp; JOIN LOAD %nameComplete, [Step Code] FROM $(vfile) (qvd);
table2_Tmp: .......... table2: LOAD * INLINE %nameComplete; table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp;

I have

table1_Tmp:

........

table1:

LOAD

%nameComplete,

...,

[Date 1],

[Date 2],

calc(Date) AS %DurationDate

RESIDENT table1_Tmp;

JOIN LOAD

%nameComplete,

[Step Code]

FROM $(vfile) (qvd);

table2_Tmp:

..........

table2:

LOAD * INLINE %nameComplete;

table2:

JOIN LOAD

%nameComplete,

[Date Creation],

...

RESIDENT table2_Tmp;

I would like to do some operations using [Date Creation] and [Date 2] and [Step Code] and %DurationDate:

I tried to do:

I tried to do:
table2_Tmp: .......... table2: LOAD * INLINE %nameComplete; table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp; OUTER JOIN LOAD [Date 1], [Date 2], %DurationDate, [Step Code], AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation RESIDENT table1;

I tried to do:

table2_Tmp:

..........

table2:

LOAD * INLINE %nameComplete;

table2:

JOIN LOAD

%nameComplete,

[Date Creation],

...

RESIDENT table2_Tmp;

OUTER JOIN LOAD

[Date 1],

[Date 2],

%DurationDate,

[Step Code],

AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation

RESIDENT table1;

But It doesn't work.

Do you know how i can une my date fields from the table1 in my table2 for the operations ?

Thanks bt advance

Re: Use field from a different table in a table

whiteline — Tue, 06 Nov 2012 17:59:32 GMT

Hi.

It seems you don't have [Date Creation] field in table1.

Re: Use field from a different table in a table

Wed, 07 Nov 2012 07:56:08 GMT

No i know, it's why i would like to join table1 to table2, to use the both fields to make my operations.

How i can do that?

Thanks by advance

Re: Use field from a different table in a table

whiteline — Wed, 07 Nov 2012 08:16:57 GMT

In QV you can operate only fields that exists in source table.

There are also way to use values from other tables (including result table) with peek and lookup functions.

To join one table to another use this kind of syntax:

join(TableNameJoinTo)

LOAD

Resident TableNameWhatToJoin;

Re: Use field from a different table in a table

Wed, 07 Nov 2012 08:57:21 GMT

Ok, i tried to do the join bu it still doesn't work for using the [Date Creation].

I'm trying to do:


table2: JOIN LOAD %nameComplete, [Date Creation], ... RESIDENT table2_Tmp; JOIN(table2) LOAD %nameComplete, [Date 1], [Date 2], %DurationDate, [Step Code], AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation RESIDENT table1;

table2:

JOIN LOAD

%nameComplete,

[Date Creation],

...

RESIDENT table2_Tmp;

JOIN(table2)

LOAD

%nameComplete,

[Date 1],

[Date 2],

%DurationDate,

[Step Code],

AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation

RESIDENT table1;

But the line:

AVG(if([Step Code] = 5, num( [Date 2] ) - num( [Date Creation] ))) AS %PhaseCreation

make me some problems ...

[Date Creation] doesn't exist, but i'm joining the table to the table where [Date Creation] exist ... so I don't understand.

Thanks by advance

Re: Use field from a different table in a table

whiteline — Wed, 07 Nov 2012 11:14:04 GMT

In QV you can operate only fields that exists in source table.

This means that [Date Creation] should already be in table1.

You didn't perform the join before as its a wrong syntax:

table2:
JOIN LOAD

should be:

JOIN(table2) LOAD

Re: Use field from a different table in a table

Wed, 07 Nov 2012 13:13:12 GMT

Thanks for the answer.

I do now JOIN(table2) LOAD for all my join tables. But it doesn't change the problem.

Unfortunatly, i can't put [Date Creation] in table1, that's why i want to make a relation between these 2 tables to have all dates together.

So i don't know how to do that, I think it's with the join but it doesn't work.

Re: Use field from a different table in a table

whiteline — Wed, 07 Nov 2012 19:01:49 GMT

The join always works. Some times not as expected...

Consider the last step that you're trying to accomplish.

The source table is table1.

So before this load the field [Date Creation] should be in table1.

This can be done with this kind of script:

join(table1)

LOAD

...some key fields to perform join properly,

representing the relation between Date Creation and table1...

[Date Creation]

Resident ...;

Unfortunatly, i can't put [Date Creation] in table1

If you know the relation. Why ?

You can drop the field from table later (drop field [FieldName] from [TableName];).

There is a good explanation of joins in QV help.

Re: Use field from a different table in a table

Mon, 12 Nov 2012 10:17:40 GMT

Okey, I can't put [Date Creation] in table1 because it's another data ffile i charge. And i have to charge this data file after the other one.

But thanks i will try to use:

You can drop the field from table later (drop field [FieldName] from [TableName];).