data duplication

[Anonymous]

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

[jonathandienst]

Maybe all you need is:

LOAD fielda,

   fieldb,

   Sum(fieldc) as fieldc,

   Min(fieldd) as fieldd

FROM ....

Group By fielda, fieldb

[Anonymous]

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

[neha_shirsath]

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

[Anonymous]

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

[surendraj]

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

[Anonymous]

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

[surendraj]

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.

[Anonymous]

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

[Anonymous]

something like :

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

[Anonymous]

how dis can be achieved.