topic Re: Removing Duplication? in QlikView

Removing Duplication?

navaneeth79 — Tue, 26 Sep 2017 14:34:46 GMT

Hi everyone,

I successfully removed duplication using group by but when I add date field I am getting duplication, how to remove the duplications in this case?

thanks in advance.

Re: Removing Duplication?

vishsaggi — Tue, 26 Sep 2017 15:22:56 GMT

Can you share some sample data you are working on and your expected output. ?

Re: Removing Duplication?

navaneeth79 — Tue, 26 Sep 2017 15:30:35 GMT

initial data

ID DATE Amount

A 25-08-2017 100

A 26-08-2017 200

A 30-08-2017 150

B 01-09-2017 200

B 10-09-2017 100

I want the output as

ID DATE Amount

A 30-08-2017 450

B 10-09-2017 300

Re: Removing Duplication?

effinty2112 — Tue, 26 Sep 2017 15:39:17 GMT

Hi,

Try:

Data:

LOAD * INLINE [

ID, DATE, Amount

A, 25-08-2017, 100

A, 26-08-2017, 200

A, 30-08-2017, 150

B, 01-09-2017, 200

B, 10-09-2017, 100

];

NoConcatenate

Aggregated:

Load

ID,

Max(DATE) as DATE,

Sum(Amount) as Amount

Resident Data Group by ID;

Drop Table Data;

To get:

ID	DATE	Amount
A	30-08-2017	450
B	10-09-2017	300

Regards

Andrew

Re: Removing Duplication?

vishsaggi — Tue, 26 Sep 2017 16:26:09 GMT

With reference to what Andrew suggested You can try this too?

Final:

LOAD ID, Date(Max(NumDate)) AS DateField, Sum(Amount) AS Amount

Group By ID;

LOAD *, Num(Floor(Date#(DATE, 'DD-MM-YYYY'))) AS NumDate INLINE [

ID, DATE, Amount

A , 25-08-2017, 100

A , 26-08-2017, 200

A , 30-08-2017, 150

B , 01-09-2017, 200

B , 10-09-2017, 100

];

Re: Removing Duplication?

its_anandrjs — Tue, 26 Sep 2017 17:20:25 GMT

What about this another one check attached also

Source:

LOAD ID,Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY') AS DATE, Amount

INLINE [

ID, DATE, Amount

A, 25-08-2017, 100

A, 26-08-2017, 200

A, 30-08-2017, 150

B, 01-09-2017, 200

B, 10-09-2017, 100

];

NoConcatenate

FinalTable:

LOAD ID,Max(DATE) as DATE, Sum(Amount) as Amount Resident Source

Group By ID Order By ID;

DROP Table Source;

Re: Removing Duplication?

navaneeth79 — Tue, 26 Sep 2017 17:23:46 GMT

Thanks for the help got the desired output.

Re: Removing Duplication?

its_anandrjs — Tue, 26 Sep 2017 17:32:05 GMT

I believe you get the answers from the thread may be close the thread with appropriate answers.

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 08:14:46 GMT

Hi ,

In the end qvw the duplicates are seen, do we have any other method to complete remove duplicates as in source file I cant remove

Re: Removing Duplication?

shraddha_g — Wed, 27 Sep 2017 08:43:02 GMT

Try date field as Date(Floor(DateField))

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 08:51:37 GMT

you mean max(date(floor(datefield)))

Re: Removing Duplication?

shraddha_g — Wed, 27 Sep 2017 09:40:25 GMT

yes

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 09:49:59 GMT

duplicates are reflecting, for better understanding I will give example

ID DATE TIME Priority Service

A 25/07/2017 100 3 XYZ

A 26/07/2017 0 3 XYZ

A 27/07/2017 10 3 XYZ

B 26/07/2017 150 3 ABC

B 28/07/2017 0 3 ABC

I want the output as

ID DATE TIME Priority Service

A 27/07/2017 110 3 XYZ

B 28/07/2017 150 3 ABC

Re: Removing Duplication?

shraddha_g — Wed, 27 Sep 2017 09:55:32 GMT

Load

ID,

Max(DATE) as DATE,

sum(TIME) as TIME,

Priority,

Service

From Table

group by ID,

Priority,

Service;

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 09:59:59 GMT

Hi Shraddha,

I did the same but getting duplicates

Re: Removing Duplication?

shraddha_g — Wed, 27 Sep 2017 10:01:02 GMT

then it will be better if you share your sample app

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 10:21:02 GMT

can youu help me again qvw application to your reply

Re: Removing Duplication?

PrashantSangle — Wed, 27 Sep 2017 11:02:24 GMT

Hi,

Can you provide your sample app???

also mention from where you want to achieve it i.e. from front end or in script..

If you want it in script then logic suggested by shraddha.g‌ must work, you have to modify it as per your requirement.

Or if you want in front end then take straight table

take ID as dimension

and expression

1:max(date)

2: sum(time)

3: max(Priority)

4: Only(Service)

Regards,

Re: Removing Duplication?

Kushal_Chawda — Wed, 27 Sep 2017 11:11:43 GMT

try this

Data:

LOAD * Inline [

ID, DATE, TIME, Priority, Service

A, 25/07/2017, 100, 3 , XYZ

A, 26/07/2017, 0 , 3 , XYZ

A, 27/07/2017, 10 , 3, XYZ

B, 26/07/2017, 150, 3 , ABC

B, 28/07/2017, 0, 3 , ABC ];

Inner Join(Data)

LOAD ID,

sum(TIME) as TimeNew,

max(DATE) as DATE

Resident Data

Group by ID;

drop field TIME;

rename field TimeNew to TIME;

Note: Replace inline table with your actual data table

Re: Removing Duplication?

navaneeth79 — Wed, 27 Sep 2017 13:11:35 GMT

thanks for the solution its working fine but a small issue in it, for particular ID two dates are same so how to get only one from it