10 Replies Latest reply: Apr 23, 2012 4:12 PM by Nicolas Agrapart

Select Field :Select item group and get all the value of the linked items

Hello everyone,

I am using qlikview since a few weeks now and I am currently confronting a case I cannot solve.

I have a product table where all my products a grouped by category. Each category has a linked category. Here is a example:

A           1             9

B           1             9

C           2             0

D           9             0

E           9             0

And each products has sales quantity in another table

Product     Quantity

A               2

B               5

C               4

D               6

E               3

I would like to present a pivot table why show the product category quantity but also the the linked category quantity:

1                    7 (5+2)           9 (6+3)

2                    4                    -

9                    9                    -

I tried different method like using Hierarchy function or P() but it does not work.

Does anyone have an idea ?

Thanks for your help

• Select Field :Select item group and get all the value of the linked items

Hi,

You can try create a flag field in fisrt table.

A           1             9          1

B           1             9          1

C           2             0          0

D           9             0          0

E           9             0           0

• Select Field :Select item group and get all the value of the linked items

My explanation may not be very clear.

1                    7 (5+2)           9 (6+3)

The 9 repressent the sum of the product who are in the linked category in this case the category 9. So the sum of the quantity of the products in this category: D+E=6+3 => 9

Thanks

• Re: Select Field :Select item group and get all the value of the linked items
Hi,
I dont know if I understood correctly but I think you could try something like that:
```TMP:
LOAD  * INLINE [
A, 1, 9
B, 1, 9
C, 2, 0
D, 9, 0
E, 9, 0
];

TMP2:
Product
RESIDENT
TMP;
LEFT JOIN
Category
RESIDENT
TMP;

Product:
NoConcatenate
*,
RESIDENT
TMP;
CONCATENATE
*,
RESIDENT
TMP2 WHERE not IsNull(Category);

DROP  TABLES TMP,TMP2;

ProductQuantity:
LOAD * INLINE [
Product, Quantity
A, 2
B, 5
C, 4
D, 6
E, 3
];

```
Then you use the expressions:

• Re: Select Field :Select item group and get all the value of the linked items

This is a simple example and I have a lot of line in my both table. So this solution won't work or I did not understood it. I created an example with qlikview with more lines:

Products:

LOAD  * INLINE [

A, 1, 9

B, 1, 9

C, 2, 0

D, 9, 0

E, 9, 0

G, 3, 2

H, 3, 2

I, 4, 0

J, 4, 0

K, 5, 4

];

ProductQuantity:

LOAD * INLINE [

Product, Quantity

A, 2

B, 5

C, 4

D, 6

E, 3

G, 7

H, 1

I, 3

J, 8

K, 10

];

What I am hoping as result is:

1,                              7,                               9

2,                              4,                               -

3,                              8,                               4

4,                              11,                               -

5,                              10,                               11

9,                              9,                               -

In SQL I may calculated the column LinkQuantity :

Select Sum(Qty) FROM ProductQuantityQ Inner Join Products P ON P.Product = Q.Product Where Category=Link as LinkQuantity

• Re: Select Field :Select item group and get all the value of the linked items

Hi,

See the attach.

Best Regards.

Tonial.

• Re: Select Field :Select item group and get all the value of the linked items

Hi Fernando,

I am not sure but I think it does not work. The 2 other columns display the same value = sum(quantity).

I would like to display the sum for the correspoonding link category.

Thanks for your help.

Best Regards,

• Re: Select Field :Select item group and get all the value of the linked items

Hi, take a look at the attachment... If you load more data it will work the same way.

Regards,
Cesar.

• Re: Select Field :Select item group and get all the value of the linked items

Hi Cesar,

You're right.

I tried it today and I was going to tell you that I misunderstood your answer and it seems to work.

It was not the answer I was hoping: I was wondering if I could do it using Set Analysis but it may be not possible or does anyone know how to do it ?

Thanks for your help

• Re: Select Field :Select item group and get all the value of the linked items

Hi nagrapart,

I made with just set Analysis too... But the linked quantities will only be shown when you select one category.

Take a look...

Regards,
Cesar

• Re: Select Field :Select item group and get all the value of the linked items

I am not this working or at least as I expected. I mean I see the results but on another line.

However thanks for your help at least with your first solution I can do something interesting:)

But I am curious to understand why it is not possible to do that using set analysis.

Thanks again Cesar