Logic Help

[BI_Dev]

I have data as below.

Date	Parent Code	Child Code	Score
9/9/2022	A	1	5
9/9/2021		A	6
9/8/2022	B	2	10

Here I want to show score by date, but we also need to look at the ID's.

Parent Code A  should be adding score for Child Code 1 and A  score, so it will be 11 and we want to show the max date of Child Code.

Parent code B will show score 10 as there is just 1 record.

Output should be as below.

Date	Parent Code	Child Code	Score
9/9/2022	A	1	11
9/8/2022	B	2	10

[ajaykakkar93]

hi,
i hope it helps

data:
load
Date,
[Parent Code],
[Child Code],
Score;
load * Inline [
Date,Parent Code,Child Code,Score
9/9/2022,A,1,5
9/9/2021,A,,6
9/8/2022,B,2,10
];

finalData:
load [Parent Code],
sum(Score) as Score,
max(Date) as Date
Group by [Parent Code];
load
[Parent Code],
[Child Code],
Score,
Date,
12 as j
Resident data;

Drop Table data;

/*

output:
Date,Parent Code,Score
9/9/2022,A,11
9/8/2022,B,10

*/