topic Connect 3 tables with different fields in QlikView

Connect 3 tables with different fields

Mon, 05 Nov 2012 15:50:54 GMT

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 — Mon, 05 Nov 2012 16:42:33 GMT

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

Tue, 06 Nov 2012 08:27:38 GMT

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

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

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

Tue, 06 Nov 2012 10:00:34 GMT

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

Tue, 06 Nov 2012 10:01:39 GMT

Thank you very much