Sum by Month, Year and ID

[Duckabush]

This is my example data

ID	Date	Amount
1	2020-05-10	$150.00
1.1	2020-06-11	$900.00
1.2	2020-05-12	$200.00
1.3	2020-07-13	$18.00
1.4	2020-05-14	$500.00
1.5	2020-06-15	$1000.00

The main ID in example is 1, the number after the period is the sub id. I need to group by ID MONTH-YEAR and sum amount for ID of MONTH-YEAR.

For example

ID	MonthYear	Sum(Amount)
1	JUL-2020	$918.00
1	JUN-2020	$1000.00
1	MAY-2020	$850.00

file:

LOAD

    SUBFIELD(ID, '.', 1)  as ID,

    date(INVOICEDATE,'YYYY-MM-DD') as Date,

    month(DATE) & '-' & year(DATE) as MonthYear

FROM [lib://file.qvd](qvd);

The second table is

file_amount:

LOAD

    subfield(id, '.', 1) as ID,

    sum(Amount) as Amount

FROM [lib://file_amount.qvd](qvd);

WHERE Amount <> '0' AND (CODE = '48' OR CODE = '50') Group By ID;

I think part of it is the Group By ID, as that is pulling the 1 and 1.1, But not sure about the rest. Any help would be great.

[pradosh_thakur]

I am not sure hat you  meant in the last but if you want table-> table 2 do the following

file:

Load ID,MonthYear,Sum(Amount) as Amount

group by ID,MonthYear;

LOAD

    SUBFIELD(ID, '.', 1)  as ID,

    date(INVOICEDATE,'YYYY-MM-DD') as Date,

    month(DATE) & '-' & year(DATE) as MonthYear,

 Amount

FROM [lib://file.qvd](qvd);