Dups management

[Anonymous]

I want to know how it works Qlickview in certain situations:

I have sales tables:

sales:

product region sales

P1       R1     100

P2       R1      200

P3       R2      300

and product table:

product:

product category

P1       C1

P2       C1

P2       C2

P3       C3

I have a duplicate by product in P2 ( 2 rows)

Qlickview  using AQL will join thw two tables by product, that will generate a dup in sales in P2 row?

It I make a SQL join it will be a dup, I want to know how Qlickview work in that case...

Thanks

[simospa]

You have the same result if you use an explicit join between table or a implicit join:

product	category	region	sales
P1	C1	R1	100
P2	C1	R1	200
P2	C2	R1	200
P3	C3	R2	300

sales:

LOAD * INLINE [

    product, region, sales

    P1,       R1,     100

    P2,       R1,      200

    P3,       R2,      300

];

join

product:

LOAD * INLINE [

    product, category

    P1, C1

    P2, C1

    P2, C2

    P3, C3

];

the data model change: if you use "join" you'll have one single entity (fact table) with all fields, if you don't use "join" you'll have two tables linked by product field.

S.

[Anonymous]

Thanks Simone,

The results of the join has dups by region and product.

I want to remove dups. I would like to order sales table by product, region and categoru

and only keep the first row of each dup key.

In this case:

C1  P1   R1   100

C1 P2   R1     200

C3  P3   R2     300

How can i do this???

Thanks in advance,

[mohammadkhatimi]

This is the result of qlikview without using join it will just link with the product as a key

Hope this will help you.

Regards,

Mohammad

[Anonymous]

Ok, Mohammad, thanks,

My problem now is that I have dups by the key: product, region and I wanto to remove duplicates.

I would like to order sales table by product, region and categoru

and only keep the first row of each dup key.

In this case:

C1  P1   R1   100

C1   P2   R1     200

C3  P3   R2     300

How can i do this???

Thanks in advance,

[simospa]

Juan,

but to have the model you reported:

C1  P1   R1   100

C1   P2   R1     200

C3  P3   R2     300

you are losing information about a set of data, in this case you have two different rows with P2 R2 and the total of sales would be 400, not 200 (and P2 is linked either to C1 and C2 category).

With which criteria do you want to show the key C1-P2-R1 and not C2-P2-R1?

S.

[#7]

Hey,

Maybe I have missed something but have you tried to use distinct in the product load to remove dupe before joining the two tables?

Edit - Miss read the second table and missed that it had unique value combinations.

[Anonymous]

Yes, but I need a unique row by product and region.

I need only a value of category for product I need to remove one of the two rows duplicates, but I don't know how

[anbu1984]

How do you define first row? Is it based on any dates?

Or Is it ok to retain any category?

[Anonymous]

it is ok to retain any category