topic Re: Dups management in QlikView

Dups management

Anonymous — Tue, 12 May 2015 10:10:12 GMT

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

Re: Dups management

simospa — Tue, 12 May 2015 10:22:16 GMT

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.

Re: Dups management

Anonymous — Tue, 12 May 2015 10:37:10 GMT

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,

Re: Dups management

mohammadkhatimi — Tue, 12 May 2015 10:42:01 GMT

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

Re: Dups management

Anonymous — Tue, 12 May 2015 10:47:16 GMT

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,

Re: Dups management

simospa — Tue, 12 May 2015 11:42:34 GMT

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?

Re: Dups management

Tue, 12 May 2015 11:43:01 GMT

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.

Re: Dups management

Anonymous — Tue, 12 May 2015 11:58:35 GMT

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

Re: Dups management

anbu1984 — Tue, 12 May 2015 12:07:16 GMT

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

Or Is it ok to retain any category?

Re: Dups management

Anonymous — Tue, 12 May 2015 12:11:42 GMT

it is ok to retain any category

Re: Dups management

simospa — Tue, 12 May 2015 12:20:06 GMT

Sorry, I don't understand which is the criteria you are using to choose C1 raither than C2 category... 😞

Re: Dups management

anbu1984 — Tue, 12 May 2015 12:49:50 GMT

LOAD product,MaxString(category) As category Group By product;
Load * Inline [
product,category
P1,C1
P2,C1
P2,C2
P3,C3 ];