4 Replies Latest reply: Nov 6, 2012 5:01 AM by guillaumek RSS

    Connect 3 tables with different fields

      Hi, I have 3 different Data Files. Inside I would like to do q relqtion with some fields:

       

      First Data File:

           - SNumber

           - Step Code (1 -> 10)

       

      Second Data File:

           - Step Code (1 -> 10)

           - Label Step Code (Label of 1 -> 10 Step Code)

       

      Third Data File:

           - SNumber

           - Current Step Code (1 -> 10)

       

      There is one Current Step Code per SNumber. But there is for each SNumber 10 Step Code, With 10 different  Label Step Code. I would like to do a relation with the Current Step Code and get the label of it.

       

      So for exemple I have:

      SNumber        Current Step Code        Step Code     Label Step Code

      10-10-555                         7                              1                    LabelPhase1

      10-10-555                         7                              2                    LabelPhase2

      10-10-555                         7                              3                    LabelPhase3

      10-10-555                         7                              4                    LabelPhase4

      10-10-555                         7                              5                    LabelPhase5

      10-10-555                         7                              6                    LabelPhase6

      10-10-555                         7                              7                    LabelPhase7

      10-10-555                         7                              8                    LabelPhase8

      10-10-555                         7                              9                    LabelPhase9

      10-10-555                         7                             10                   LabelPhase10

       

      I would like to retreive the Label Step Code for the Current Step Code. So i would like to have:

      SNumber         Current Step Code  Label Current Step Code  Step Code       Label Step Code

      10-10-555                         7                             LabelPhase7                         1                    LabelPhase1

      10-10-555                         7                             LabelPhase7                         2                    LabelPhase2

      10-10-555                         7                             LabelPhase7                         3                    LabelPhase3

      10-10-555                         7                             LabelPhase7                         4                    LabelPhase4

      10-10-555                         7                             LabelPhase7                         5                    LabelPhase5

      10-10-555                         7                             LabelPhase7                         6                    LabelPhase6

      10-10-555                         7                             LabelPhase7                         7                    LabelPhase7

      10-10-555                         7                             LabelPhase7                         8                    LabelPhase8

      10-10-555                         7                             LabelPhase7                         9                    LabelPhase9

      10-10-555                         7                             LabelPhase7                        10                   LabelPhase10

       

      Is it possible to do this relation in the script and how please?

       

      Thanks by advance

        • Re: Connect 3 tables with different fields
          whiteline _

          Hi

           

          Just load [Step Code Label] and [Step Code] (and SNumber if Phases are different) into separate table with 'distinct' statement.

           

          Then you can just join it renaming the field [Step Code       Label] as [Current Step Code       Label].

            • Re: Connect 3 tables with different fields

              Thanks for your answer. I tried to do that; the problem is i have a unique field in my table for the label if i do that.

               

              I have:

               

              Table1:

              LOAD

                   SNumber ,

                   %Filename,

                    [Service Code],

                    [Step Code]

              FROM $(vFile) (qvd);

               

              Table 2:

              LOAD

                   [Step Code],

                   [Step Code QV],

                   [Label Step Code]              

              FROM $(vFile) (qvd);

               

              Table3:

              LOAD

                   SNumber ,

                   Current Step Code,

                   ....,

                   [Ref. Commerciale]

              FROM $(vFile) (qvd);

               

              I will have ?:

               

              Table1:

              LOAD:

                   SNumber ,

                   %Filename,

                    [Service Code],

                    [Step Code]

              FROM $(vFile) (qvd);

               

              Table 2:

              LOAD

                   [Step Code],

                   [Step Code QV],

                   [Label Step Code]  AS  [ Curent Label Step Code] ,

              FROM $(vFile) (qvd);

               

              Table 2 - 1:

              LOAD DISTINCT

                   [Label Step Code] 

              FROM $(vFile) (qvd);

               

              Table3:

              LOAD

                   SNumber ,

                   Current Step Code,

                   ....,

                   [Ref. Commerciale]

              FROM $(vFile) (qvd);

               

              But i doesn't work for the moment. It duplicate all my SNumber in 10 ..

               

              Do you have an Idea?

               

              Thanks by advance

            • Re: Connect 3 tables with different fields

              Okey, It seems works like you say:

              I did a join load distinct, i just have to do a load distinct:

               

              Table1:

              LOAD:

                   SNumber ,

                   %Filename,

                    [Service Code],

                    [Step Code]

              FROM $(vFile) (qvd);

               

              Table 2:

              LOAD

                   [Step Code],

                   [Step Code QV],

                   [Label Step Code]  ,

              FROM $(vFile) (qvd);

               

              LOAD DISTINCT

                   [Label Step Code] AS [Current Label Step Code],

                   [Step Code] AS [Current Step Code]

              FROM $(vFile) (qvd);

               

              Table3:

              LOAD

                   SNumber ,

                   Current Step Code,

                   ....,

                   [Ref. Commerciale]

              FROM $(vFile) (qvd);

              • Re: Connect 3 tables with different fields

                Thank you very much