hello,
i wanted to ask for your help to create from an already existing table another one with certain conditions (2).
that conditions would be the equivalente to the "ADD IF SET" formula in EXCEL.
in this new table i want to sum the field Amount only if the field days_until_expiration(old table) is greater than the field Aux (new table). the second condition would be that this has to be done for every date, considering the values of that specific date.
in my attemp I cant code the condition "days_until_expiration" > "Aux".
I attached an excel file with the results i'm looking for. thank you in advance!
original_table:
Load * Inline [
product_id,date,days_until_expiration,amount,time_band
1,'2020-03-31',20,100,'1M'
2,'2020-03-31',40,200,'2M'
3,'2020-03-31',70,300,'3M'
4,'2020-03-31',110,400,'4M'
5,'2020-03-31',130,500,'5M'
2,'2020-03-31',10,200,'1M'
3,'2020-03-31',40,300,'2M'
4,'2020-03-31',80,400,'3M'
5,'2020-03-31',100,500,'4M'
];
For Each i in FieldValueList('date')
New_table:
Load distinct date as date2
Resident original_table;
Left Join (New_table)
Load * Inline [
Banda,Aux
1M,0,
2M,31,
3M,61
4M,91
5M,121
6M,151
7M,181
8M,211
9M,241
10M,271
11M,301
12M,331
];
next i;
Left Join (New_table)
Load
sum(if(days_until_expiration>Aux,amount,0)) as surviving
Resident [original_table] ;
