Connect 3 tables with different fields

[#1]

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

[whiteline · Accepted solution]

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

[#3]

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

[#4]

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

[#5]

Thank you very much