Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Hope you all doing great.!!!
I want to create a new aggregated column for the Product age is at Zero and that value should update all the cell values with different ages.
Below is the logic I have used, I know this is not correct:
AgeTable:
load * Inline [
ID, PAge, sales
A1, 0, $50
A2, 1, $100
A3, 2, $200
B1, 0, $30
B2, 1, $100
];
left Join(AgeTable)
load ID as ID,
PAge as UAge,
sales as Usales
Resident AgeTable
where PAge = 0
Result:
Result Expectin:
ID | Age | sales | Sales at age Zero |
A1 | 0 | $50 | $50 |
A2 | 1 | $100 | $50 |
A3 | 2 | $200 | $50 |
B1 | 0 | $30 | $30 |
B2 | 1 | $100 | $30 |
I understand what you want to do, you want all the ids that begin with the letter A (a0, a1, a2, a3, a4) to add the value of a0 ($ 50) in the USALES field, this could be added using a letter to group, it would be as follows:
AgeTable:
load *,
left (ID, 1) as group;
load * Inline [
ID, PAge, sales
A1, 0, $ 50
A2, 1, $ 100
A3, 2, $ 200
B1, 0, $ 30
B2, 1, $ 100
];
left Join (AgeTable)
load
group,
// ID as ID,
PAge as UAge,
Sales as Usales
Resident AgeTable
where PAge = 0;
this should work for you.
Regards!!!
Or try
AgeTable_AUX:
load * Inline [
ID, PAge, sales
A1, 0, $ 50
A2, 1, $ 100
A3, 2, $ 200
B1, 0, $ 30
B2, 1, $ 100
];
AgeTable:
load *,
left (ID, 1) as group
resident AgeTable_AUX;
DROP TABLE AgeTable_AUX;
left Join (AgeTable)
load
group,
// ID as ID,
PAge as UAge,
Sales as Usales
Resident AgeTable
where PAge = 0;