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

[#1]

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:

Product Category Link

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:

Category     QuantityCat     QuantityLinked

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

[fernando_tonial]

Hi,

You can try create a flag field in fisrt table.

Product Category Link FlgLink

A           1             9          1

B           1             9          1

C           2             0          0

D           9             0          0

E           9             0           0

The expression QuantityLinked = Sum(Quantity*FlgLink)

[#3]

My explanation may not be very clear.

Category     QuantityCat     QuantityLinked

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

[cesaraccardi]

Hi,
I dont know if I understood correctly but I think you could try something like that:

TMP:
LOAD  * INLINE [
    Product, Category, Link
    A, 1, 9
    B, 1, 9
    C, 2, 0
    D, 9, 0
    E, 9, 0
];


TMP2:
LOAD  Distinct
     Category as Link,
     Product
RESIDENT
     TMP;     
LEFT JOIN
LOAD 
     Link,
     Category
RESIDENT
TMP;

Product:
NoConcatenate
LOAD 
     *,
     0 as Linked
RESIDENT
     TMP;
CONCATENATE
LOAD 
     *,
     1 as Linked     
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:

sum({$<Linked={0}>} Quantity) and sum({$<Linked={1}>} Quantity)

Hope this help you.

[#5]

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 [

    Product, Category, Link

    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:

Cat,                     Quantity,           LinkQuantity

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

[fernando_tonial]

Hi,

See the attach.

Best Regards.

Tonial.

[#7]

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,

[cesaraccardi]

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

Regards,
Cesar.

[#9]

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

[cesaraccardi]

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