topic Re: Data model obviously wrong in Connectivity & Data Prep

Data model obviously wrong

Wed, 08 Jun 2016 15:34:10 GMT

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

Re: Data model obviously wrong

Gysbert_Wassenaar — Wed, 08 Jun 2016 16:15:29 GMT

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

Re: Data model obviously wrong

Wed, 08 Jun 2016 16:59:17 GMT

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

Re: Data model obviously wrong

ramoncova06 — Wed, 08 Jun 2016 17:59:14 GMT

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 ?

Re: Data model obviously wrong

Gysbert_Wassenaar — Wed, 08 Jun 2016 18:24:54 GMT

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

Re: Data model obviously wrong

Thu, 09 Jun 2016 06:05:56 GMT

Thank you Gysbert, you were very helpfull !

Damien