topic Re: Link table in QlikView

Link table

Thu, 03 Apr 2014 09:36:36 GMT

Hi a have three tables. How to create a report that shows sales that will contain the name from "Product Name" and Text from "Code Table". In addition, as shown in a separate label CodeTable for CatNr 1000 and separately CatNr 2000 - how to make a filter?

CodeTable

CatNr	Code	Text
1000	1	Message
1000	2	Information
2000	1	Car
2000	2	House

Product Name

ID	Name	CatNr	Code
F7000	ABC	1000	1
F7003	DEF	2000	2
F7004	GHI	3000	1

Product

Sale	ID_Product_Name
266456,32	F7000
456324,00	F7003

what I want to get:

Catalog 1
Message
Information

Catalog 2
Car
House

Name	Cat	SumSale
DEF	Hause	4561231,32
ABC	Message	266456,32

Re: Link table

datanibbler — Thu, 03 Apr 2014 09:49:57 GMT

Hi,

just do a JOIN between those two tables (in two steps - first join "Product Name" to "CodeTable", then join "Product" to that..

QlikView automatically joins on the field(s) that have the same name, so you have to take care that only the field that represents your key (>Code<?) has the same name in both tables.

HTH

Best regards,

DataNibbler

Re: Link table

its_anandrjs — Thu, 03 Apr 2014 09:54:53 GMT

Load like below script

CodeTable

left join

Product Name

left join

Product

Re: Link table

its_anandrjs — Thu, 03 Apr 2014 10:05:37 GMT

See load script how to load

CodeTable:

LOAD CatNr1, Code, Text

Left Join

ProductName:

LOAD ID, Name, CatNr, Code

Left Join

Product:

LOAD Sale, ID_Product_Name as ID

Re: Link table

Thu, 03 Apr 2014 10:28:23 GMT

the problem is with the catalogs. Since I have several of them, and the value in column Code is repeated then I can not filter properly.When I have only one directory everything is OK when I do condition where I have a problem

CodeTable:

LOAD CatNr1, Code, Text

where CatNr =1000 or CatNr=2000

Left Join

ProductName:

LOAD ID, Name, CatNr, Code

Left Join

Product:

LOAD Sale, ID_Product_Name as ID

Re: Link table

datanibbler — Thu, 03 Apr 2014 10:39:11 GMT

You have to make sure the field you're going to join on is unique

- in both tables if you're doing an INNER JOIN (standard in QV afaIk)

- in the second table if you're doing a LEFT JOIN

=> So, dep. on your requirements, you might have to work with several RÉSIDENT LOADs, to generate a table with both years' sales first

=> then you could join that to the table_with_multiple_catalogs (thus joining both years' prices to every line concerning a specific product)

HTH

Re: Link table

its_anandrjs — Thu, 03 Apr 2014 10:44:06 GMT

Provide some sample data for this other wise name a composite key in both tables like

CodeTable:

LOAD CatNr1, Code, Text,CatNr&'|'&Code as Key

Left Join

ProductName:

LOAD ID, Name, CatNr, Code,CatNr&'|'&Code as Key

will give you only 1000 and 2000