linking 2 tables

[marcorizzo]

Hi everybody.

I've got a simple question (but difficult for me).

I've got this 2 sql tables.

tab1:

Code	Product
1	cap
2	jeans
3	coat
4	other

tab2:

Customer	Product1	Product2	Product3
mark	1	null	3
paul	2	null	null
jenny	1	4	2

How can I link them, so that I can obtain (in qvw document) this final table ?

Header 1	Header 2	Header 3	Header 4
mark	cap	null	coat
paul	jeans	null	null
jenny	cap	other	jeans

Thank you so much

Marco

[Sokkorn]

Hi Marco,

My idea is using ApplyMap() function. Something like this:

[Product]:

Mapping

Load

     Code,

     Product;

Select Code, Product From ProductTable;

[Customer]:

Load

     Customer,

     ApplyMap('Product',Product1,Null())     AS [Product1],

     ApplyMap('Product',Product2,Null())     AS [Product2],

     ApplyMap('Product',Product3,Null())     AS [Product3];

Select Customer, Product1, Product2, Product3 From CustomerTable;

You need to change table name accordingly.

Regards,

Sokkorn

[marcorizzo]

Thank you. I'll try as soon as possible.

And what about a join inside qlikview's script ?

P.S. Sorry for my English

[Clever_Anjos]

You could use a join like this, but Sokkorn approach is better

[Product]:

Load

     Code,

     Product;

Select Code, Product From ProductTable;

[Customer]:

Load

     Customer,

      [Product1],

     [Product2],

      [Product3];

Select Customer, Product1, Product2, Product3 From CustomerTable;

Left join(Customer)

LOAD

     Code as Product1,

     Product as ProductDescription1

resident Product;

Left join(Customer)

LOAD

     Code as Product2,

     Product as ProductDescription2

resident Product;

Left join(Customer)

LOAD

     Code as Product3,

     Product as ProductDescription3

resident Product;

[Sokkorn]

Hi Marco,

If you wish join table, you can try suggestion from Clever Anjos. For me I prefer ApplyMap() because it have better load performance.

Regards,

Sokkorn

[marcorizzo]

Sorry but I have some difficulties. This was my real script :

INF01:

LOAD *;

SQL SELECT   

    "INF01_CATEGORIA_INF03" AS [Codice Categoria],

    "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

    "INF01_DITTA" as [Ditta Crm],

    "INF01_MACRO_INF02" as [Codice Macrocategorie],

    "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

    "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

    "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

    "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

    "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

FROM GRUPPOINFOR.dbo."INF01_CONTATTI";

INF04:

LOAD *;

SQL SELECT

        "INF04_CODICE" AS [Codice Prodotti],

        "INF04_DESCRIZIONE" as [Descrizione Prodotti]

FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

I want to link

    "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

    "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

    "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

    "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

    "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

with

"INF04_CODICE" AS [Codice Prodotti].

This is the way I set ApplyMap function. What is wrong ?

INF04:

Mapping

LOAD

     "INF04_CODICE" AS [Codice Prodotti],

        "INF04_DESCRIZIONE" as [Descrizione Prodotti];

SQL SELECT

        "INF04_CODICE" AS [Codice Prodotti],

        "INF04_DESCRIZIONE" as [Descrizione Prodotti]

FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

INF01:

LOAD

"INF01_CATEGORIA_INF03" AS [Codice Categoria],

    "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

    "INF01_DITTA" as [Ditta Crm],

    "INF01_MACRO_INF02" as [Codice Macrocategorie],

    ApplyMap("INF04_CODICE","INF01_PRODOTTO1_INF04", null()) AS [Codice Prodotti1],

    ApplyMap("INF04_CODICE","INF01_PRODOTTO2_INF04", null()) AS [Codice Prodotti2],

    ApplyMap("INF04_CODICE","INF01_PRODOTTO3_INF04", null()) AS [Codice Prodotti3],

    ApplyMap("INF04_CODICE","INF01_PRODOTTO4_INF04", null()) AS [Codice Prodotti4],

    ApplyMap("INF04_CODICE","INF01_PRODOTTO5_INF04", null()) AS [Codice Prodotti5];

SQL SELECT    

    "INF01_CATEGORIA_INF03" AS [Codice Categoria],

    "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

    "INF01_DITTA" as [Ditta Crm],

    "INF01_MACRO_INF02" as [Codice Macrocategorie],

    "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

    "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

    "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

    "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

    "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

FROM GRUPPOINFOR.dbo."INF01_CONTATTI";

I've got the message :

Field not found- <INF04_CODICE>

SQL SELECT

        "INF04_CODICE" AS [Codice Prodotti],

        "INF04_DESCRIZIONE" as [Descrizione Prodotti]

FROM GRUPPOINFOR.dbo."INF04_PRODOTTI"

[MayilVahanan]

hi

you replace "INF04_CODICE" AS [Codice Prodotti];

So try like this

LOAD

     [Codice Prodotti],

        [Descrizione Prodotti];

SQL SELECT

        "INF04_CODICE" AS [Codice Prodotti],

        "INF04_DESCRIZIONE" as [Descrizione Prodotti]

FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

[marcorizzo]

Hi. Maybe I'm not able to explain well what I need.

I give you an easier example.

file1 script

tab1:

load*;

SQL SELECT codice as [codice prodotto],

    prodotto as [descrizione prodotto]

FROM GRUPPOINFOR.dbo.Tabella1;

tab2:

load*;

SQL SELECT codicecliente as [codice cliente],

    codprodotto1 as [codice prodotto 1],

    codprodotto2 as [codice prodotto 2],

    codprodotto3 as [codice prodotto 3],

    codprodotto4 as [codice prodotto 4]

FROM GRUPPOINFOR.dbo.Tabella2;

The result is image1.

file2 script (with ApplyMap function):

tab1:

Mapping

Load

    [codice prodotto],

    [descrizione prodotto];

SQL SELECT codice as [codice prodotto],

    prodotto as [descrizione prodotto]

FROM GRUPPOINFOR.dbo.Tabella1;

tab2:

Load

    [codice cliente],

    ApplyMap('[codice prodotto]',[codice prodotto 1],null()) as [codice prodotto 1],

    ApplyMap('[codice prodotto]',[codice prodotto 2],null()) as [codice prodotto 2],

    ApplyMap('[codice prodotto]',[codice prodotto 3],null()) as [codice prodotto 3],

    ApplyMap('[codice prodotto]',[codice prodotto 4],null()) as [codice prodotto 4];

SQL SELECT codicecliente as [codice cliente],

    codprodotto1 as [codice prodotto 1],

    codprodotto2 as [codice prodotto 2],

    codprodotto3 as [codice prodotto 3],

    codprodotto4 as [codice prodotto 4]

FROM GRUPPOINFOR.dbo.Tabella2;

The result is image2. This show only one table (image2a) instead first case (image1a).

I'd like to obtain this:

codice cliente	prodotto 1	prodotto 2	prodotto 3	prodotto 4
1	evolve	gamma	metodo
2	gamma	metodo
3	evolve	paghe
4	metodo	paghe
5	paghe	gamma

THANK YOU VERY VERY MUCH ...

Marco

[#9]

Hi Marco,

The first parameter of ApplyMap should be the mapping table name.

So based on your example, it should be:

ApplyMap('tab1',  [codice prodotto 1], null()) as [codice prodotto 1]