topic Re: data duplication in QlikView

data duplication

Anonymous — Wed, 06 Sep 2017 13:56:57 GMT

fielda	fieldb	fieldc	fieldD
2	abc	2	123
2	abc	2	456
2	dfg	3	123
2	dfg
2	hij	4	123
2	hij	4	456
2	klm	5	123
2	klm	5	456
2	nop	7	123
2	nop	7	456

output shud be :

2	abc	2	123
2	hij	4	123
2	klm	5	123
2	nop	7	123

Re: data duplication

sunny_talwar — Wed, 06 Sep 2017 14:03:35 GMT

May be load with DISTINCT keyword

LOAD Distinct

fielda,

fieldb,

fieldc

FROM ....

Re: data duplication

Anonymous — Wed, 06 Sep 2017 14:30:40 GMT

taking only distinct will not work.

Re: data duplication

marcus_sommer — Wed, 06 Sep 2017 14:37:35 GMT

What is the condition for "fieldD"?

- Marcus

Re: data duplication

datagrrl — Wed, 06 Sep 2017 14:43:42 GMT

I have some questions about your model. Is this a single flat table? Is the duplication in the data or being caused by the association?

Do you want the max value of fieldc?

Re: data duplication

Anonymous — Thu, 07 Sep 2017 05:26:33 GMT

it is just the single table now,

Re: data duplication

Anonymous — Thu, 07 Sep 2017 05:47:43 GMT

kindly help with the resolution

Re: data duplication

Anonymous — Thu, 07 Sep 2017 06:38:26 GMT

duplicacy should be removed from field b

Re: data duplication

Anonymous — Thu, 07 Sep 2017 06:40:48 GMT

i want sum of fieldc

Re: data duplication

Kushal_Chawda — Thu, 07 Sep 2017 07:12:10 GMT

try this

Data:

LOAD Distinct

fielda,

fieldb,

fieldc,

fieldd

FROM ...

Final

LOAD

fielda,

fieldb,

fieldc,

min(fieldd) as fieldd

resident Data

group by fielda,fieldb,fieldc;

drop table Data;

Re: data duplication

jonathandienst — Thu, 07 Sep 2017 07:13:47 GMT

Maybe all you need is:

LOAD fielda,

fieldb,

Sum(fieldc) as fieldc,

Min(fieldd) as fieldd

FROM ....

Group By fielda, fieldb

Re: data duplication

Anonymous — Thu, 07 Sep 2017 07:16:42 GMT

As per the data provided by you below script gives the desired output:

LOAD

Distinct FieldA, FieldB, FieldC, FieldD
FROM [SampleData.xlsx](ooxml, embedded labels)

where FieldD=123;

Assumptions are

1. Condition on FieldD is that it should be equal to 123

2. SampleData.xlsx is the XL file with the data provided by you

Regards,

Kaushik Das

Re: data duplication

neha_shirsath — Thu, 07 Sep 2017 07:42:56 GMT

In your output table you are not displaying dfg value from Fieldb. Is there any logic to remove this and take selected distinct records?

Re: data duplication

Anonymous — Sun, 10 Sep 2017 09:21:48 GMT

Thnks everyone for the response,but wat m trying to do now is,need to remove these values from field b who have multiple values against field a ,for eg field a field b,field a has 2 value in field b is ABC,cde and one more where field a has 3 and in field b is ghk which is fine

Re: data duplication

surendraj — Sun, 10 Sep 2017 11:52:50 GMT

tab1:

LOAD *,if(fieldb=Previous(fieldb),1,0) as flag;

load * INLINE [

fielda, fieldb, fieldc, fieldD

2, abc, 2, 123

2, abc, 2, 456

2, dfg, 3, 123

2, dfg

2, hij, 4, 123

2, hij, 4, 456

2, klm, 5, 123

2, klm, 5, 456

2, nop, 7, 123

2, nop, 7, 456

];

NoConcatenate

load *

Resident tab1

Where flag=0;

drop Table tab1;

--Surendra

Re: data duplication

Anonymous — Sun, 10 Sep 2017 12:56:48 GMT

thanks,but what i want is

drop those values of field b which are more than 1 against fielda

fielda fieldb

1 abc

1 def

2 jhk

keep the value jhk

while drop abc and def

Re: data duplication

surendraj — Sun, 10 Sep 2017 18:42:20 GMT

In your Data set ,you don't have values( def and jhk) in filedb.

if you go through my answer i eliminated ll the duplicate rows based on fieldb by make use of flag.

Re: data duplication

Anonymous — Mon, 11 Sep 2017 05:34:35 GMT

ohkk,

the data set is kind of this for eg:

fielda fieldb

1 abc

1 def

2 jhk

keep the value jhk

while drop abc and def

jhk has one value against field a

while abc and def have two rows against field a

Re: data duplication

Anonymous — Mon, 11 Sep 2017 05:44:48 GMT

something like :

if(count(fielda) =1 and fieldb >1 )

Re: data duplication

Anonymous — Mon, 11 Sep 2017 06:20:05 GMT

how dis can be achieved.