Data model obviously wrong

[#1]

Hi,

First of all, I'm new to QlikView, so please excuse my ignorance. Also I'm french, so please excuse my english

I'm trying to load some datas to build some tables. Right now, I'm doing like this (and it's not working, obviously):

[TIRS]:

LOAD

[NUMERO_TIR],

[NUMERO_REFERENCE]

  FROM [lib://Indus/2.0/tirs.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[METRIQUES]:

LOAD

[NUMERO_TIR],

[METRIQUE],

[VALEUR_METRIQUE]

  FROM [lib://Indus/2.0/metriques.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

NUMERO_TIR is a unique number, and NUMERO_REFERENCE is a number equal to a NUMERO_TIR. It can only be a NUMERO_TIR, not another value.

I would like to import those data and display the following in a table :

METRIQUE | VALEUR_METRIQUE | VALEUR_REFERENCE

It's easy to get METRIQUE and VALEUR_METRIQUE for a NUMERO_TIR, But I can reach to VALEUR_REFERENCE, which is a VALEUR_METRIQUE for another NUMERO_TIR, referenced by NUMERO_REFERENCE.

I'm not sure if I'm clear, let me know if I'm not.

I don't have any unique key in METRIQUES, but I don't think it's the problem. I can't think of another model, can someone give me some advices ?

Damien

[Gysbert_Wassenaar]

Try this:

[TIRS]:

LOAD

[NUMERO_TIR],

[NUMERO_REFERENCE]

  FROM [lib://Indus/2.0/tirs.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[METRIQUES]:

LOAD

[NUMERO_TIR],

[METRIQUE],

[VALEUR_METRIQUE]

  FROM [lib://Indus/2.0/metriques.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[METRIQUES_REFERENCE]:

LOAD

[NUMERO_TIR] AS [NUMERO_REFERENCE],

[VALEUR_METRIQUE] AS VALEUR_REFERENCE

  FROM [lib://Indus/2.0/metriques.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

[#3]

By reading your response, I think I may have forgotten an important detail : I have several METRIQUE for a NUMERO_TIR.

Maybe I'll be more clear with example tables :

Table "TIRS"

NUMERO_TIR	NUMERO_REFERENCE
1
2
3	1
4	1
5	2
6	1
7	2
8	5
9	6
10	5

Table "METRIQUES"

NUMERO_TIR	METRIQUE	VALEUR_METRIQUE
1	Metric_1	Value_1
1	Metric_2	Value_2
1	Metric_3	Value_3
1	Metric_4	Value_4
2	Metric_1	Value_5
2	Metric_5	Value_6
3	Metric_1	Value_7
3	Metric_2	Value_8
3	Metric_3	Value_9
3	Metric_4	Value_10

And eventually, the result I'd like, by selecting a NUMERO_TIR (NUMERO_TIR = 3 in this example) :

METRIQUE	VALEUR_METRIQUE	VALEUR_REFERENCE
Metric_1	Value_7	Value_1
Metric_2	Value_8	Value_2
Metric_3	Value_9	Value_3
Metric_4	Value_10	Value_4

[ramoncova06]

Did you try Gysbert option ?

I am not able to understand on your example the value reference as I do not see that nowhere, is this a calculated field ?

[Gysbert_Wassenaar · Accepted solution]

Ok, try this:

[TIRS]:
LOAD NUMERO_TIR,
    NUMERO_REFERENCE
FROM
[https://community.qlik.com/thread/220028]
(html, codepage is 1252, embedded labels, table is @1);

[METRIQUES]:
LOAD NUMERO_TIR,
    METRIQUE,
    VALEUR_METRIQUE
FROM
[https://community.qlik.com/thread/220028]
(html, codepage is 1252, embedded labels, table is @2);

mapRef:
MAPPING LOAD NUMERO_REFERENCE, NUMERO_TIR RESIDENT TIRS;

LEFT JOIN ([METRIQUES])

LOAD ApplyMap('mapRef',NUMERO_TIR, Null()) AS NUMERO_TIR,     
  METRIQUE,
    VALEUR_METRIQUE AS VALEUR_REFERENCE
FROM
[https://community.qlik.com/thread/220028]
(html, codepage is 1252, embedded labels, table is @2);

[#6]

Thank you Gysbert, you were very helpfull !

Damien