Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Load like below script
CodeTable
left join
Product Name
left join
Product
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
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
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
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