Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
navaneeth79
Contributor II
Contributor II

Removing Duplication?

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.

1 Solution

Accepted Solutions
its_anandrjs
Champion III
Champion III

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;

View solution in original post

20 Replies
vishsaggi
Champion III
Champion III

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

navaneeth79
Contributor II
Contributor II
Author

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
Master
Master

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
A30-08-2017450
B10-09-2017300

Regards

Andrew

vishsaggi
Champion III
Champion III

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
Champion III
Champion III

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
Contributor II
Contributor II
Author

Thanks for the help got the desired output.

its_anandrjs
Champion III
Champion III

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

navaneeth79
Contributor II
Contributor II
Author

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
Partner - Master III
Partner - Master III

Try date field as Date(Floor(DateField))