topic Re: Mapping tables in QlikView

Mapping tables

thanhphongle — Thu, 27 Apr 2017 13:35:28 GMT

Hello Community

I have several tables wich I would like to map to my Table "Main" in the script.

These are my tables

Table Product

Product ID	Productname	Category
P10001	A	F
P10002	B	G
P10003	C	H
P10004	D	I
P10005	E	J

Table facility

Location ID	City	Country
L10001	Berlin	Germany
L10004	Mosow	Russia
L10005	Tokyo	Japan

Table Main

C ID	Name	Product ID	Location ID	Cost	Quantity
C1001	n1	P10001	L10001	100	1
C1002	n2	P10001	L10001	200	2
C1003	n3	P10001	L10001	300	3
C1004	n4	P10002	L10001	400	4
C1005	n5	P10002	L10001	200	5
C1006	n6	P10003	L10004	100	6
C1007	n7	P10003	L10004	100	7
C1008	n8	P10003	L10005	150	8
C1009	n9	P10003	L10004	160	5
C1010	n10	P10004	L10005	170	6
C1011	n11	P10005	L10005	180	2
C1012	n12	P10005	L10005	190	2

First of all, is a mapping function in the script really needed because QV is actually setting the relation between the tables. When I count unique products, QV returns me the real quantity + 1 which is wrong. Can anyone explain me why QV is doing it?

I used the function count(distinct ([Product_ID]))

If there s a mapping needed how can I do it?

And another question:

Is it possible to count datas from certain tables? Example:

count(Product ID) where Table = Main -> 12

count(Product ID) where Table = Product -> 5

Re: Mapping tables

awhitfield — Thu, 27 Apr 2017 15:19:51 GMT

First of all, is a mapping function in the script really needed because QV is actually setting the relation between the tables.

No - that's not require

Count(distinct product_id_ returns 5

Count(product_id) returns 12

HTH

Andy

Re: Mapping tables

thanhphongle — Thu, 27 Apr 2017 16:04:21 GMT

Actually I have another database which I cant upload.

For this case I created a new attribut called unique_id_product and let it count through starting by 1

in my expression I always have to add the if function

count(if(unique_id_product>0, [Product_ID])

If I dont add the if expression it does not count me the right number of products in the Main table.

So is there an expression where I can choose the the attribute and the referring table.

I know that QlikSense automatically adds the table name behind the attribut if you have two same attribut from different tables.

Example

Product_ID = Table Main

Product_ID = Table Product

QlikSense

[Product_ID.Main]

[Product_ID.Product] somehow like that.

Re: Mapping tables

MindaugasBacius — Fri, 28 Apr 2017 07:55:52 GMT

Could you implement unique_id_product into the above example you've shared?