Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Use field from a different table in a table

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

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

8 Replies
whiteline
Honored Contributor II

Re: Use field from a different table in a table

Hi.

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

Not applicable

Re: Use field from a different table in a table

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

whiteline
Honored Contributor II

Re: Use field from a different table in a table

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;

Not applicable

Re: Use field from a different table in a table

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;

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

whiteline
Honored Contributor II

Re: Use field from a different table in a table

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

Not applicable

Re: Use field from a different table in a table

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.

whiteline
Honored Contributor II

Re: Use field from a different table in a table

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.


Not applicable

Re: Use field from a different table in a table

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];).