Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
marcorizzo
New Contributor III

linking 2 tables

Hi everybody.

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

I've got this 2 sql tables.

tab1:

Code
Product
1cap
2jeans
3coat
4other

tab2:

Customer
Product1Product2Product3
mark1null3
paul2nullnull
jenny142

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

Header 1Header 2Header 3Header 4
markcapnullcoat
pauljeansnullnull
jennycapotherjeans

Thank you so much

Marco

8 Replies
Sokkorn
Honored Contributor

Re: linking 2 tables

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
New Contributor III

Re: linking 2 tables

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

And what about a join inside qlikview's script ?

P.S. Sorry for my English

Employee
Employee

Re: linking 2 tables

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
Honored Contributor

Re: linking 2 tables

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
New Contributor III

Re: linking 2 tables

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"

Re: linking 2 tables

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
New Contributor III

Re: linking 2 tables

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 1prodotto 2prodotto 3prodotto 4
1evolvegammametodo
2gammametodo
3evolvepaghe
4metodopaghe
5paghegamma

THANK YOU VERY VERY MUCH ...

Marco

Not applicable

Re: linking 2 tables

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]

Community Browser