Removing Duplication?

[navaneeth79]

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.

[vishsaggi]

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

[navaneeth79]

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

[effinty2112]

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

[vishsaggi]

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

];

[its_anandrjs · Accepted solution]

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;

[navaneeth79]

Thanks for the help got the desired output.

[its_anandrjs]

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

[navaneeth79]

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

[shraddha_g]

Try date field as Date(Floor(DateField))