Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thank you. I'll try as soon as possible.
And what about a join inside qlikview's script ?
P.S. Sorry for my English
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;
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
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"
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";
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
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]