8 Replies Latest reply: Nov 12, 2012 5:17 AM by guillaumek RSS

    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

        • Re: Use field from a different table in a table
          whiteline _

          Hi.

           

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

            • 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

                • Re: Use field from a different table in a table
                  whiteline _

                  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

                      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

                        • Re: Use field from a different table in a table
                          whiteline _

                          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

                              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 _

                                  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.