Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Can you share some sample data you are working on and your expected output. ?
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
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
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
];
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;
Thanks for the help got the desired output.
I believe you get the answers from the thread may be close the thread with appropriate answers.
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
Try date field as Date(Floor(DateField))