topic linking 2 tables in QlikView

linking 2 tables

marcorizzo — Mon, 02 Dec 2013 23:53:39 GMT

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

Re: linking 2 tables

Sokkorn — Tue, 03 Dec 2013 01:35:36 GMT

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

Re: linking 2 tables

marcorizzo — Tue, 03 Dec 2013 14:56:55 GMT

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

And what about a join inside qlikview's script ?

P.S. Sorry for my English

Re: linking 2 tables

Clever_Anjos — Tue, 03 Dec 2013 15:09:38 GMT

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;

Re: linking 2 tables

Sokkorn — Wed, 04 Dec 2013 01:01:58 GMT

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

Re: linking 2 tables

marcorizzo — Wed, 04 Dec 2013 19:54:43 GMT

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

MayilVahanan — Wed, 04 Dec 2013 20:22:58 GMT

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

Re: linking 2 tables

marcorizzo — Wed, 04 Dec 2013 22:28:15 GMT

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
1	evolve	gamma	metodo
2	gamma	metodo
3	evolve	paghe
4	metodo	paghe
5	paghe	gamma

THANK YOU VERY VERY MUCH ...

Marco

Re: linking 2 tables

Thu, 05 Dec 2013 01:52:44 GMT

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]