Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!
I have this kind of table:
How can I have a third column with values of the last day of each month? Something like this:
Thanks in advance!
Values:
LOAD Date,
Value,
Month(Date) as Month
;
LOAD * INLINE [
Date, Value
07/01/2015, 423
07/10/2015, 567
07/15/2015, 678
07/20/2015, 45
07/31/2015, 678
08/01/2015, 4576
08/10/2015, 657
08/14/2015, 4132
08/21/2015, 6789
08/27/2015, 213
08/31/2015, 3467
];
JOIN
LOAD Month as Month,
Max(Date) as Date,
1 as IsMaxDate
RESIDENT Values
GROUP BY Month;
LEFT JOIN
LOAD Month,
Value as LasDayValue
RESIDENT Values
WHERE IsMaxDate =1;
DROP Field IsMaxDate From Values
If you have a Month field and you add that as a dimension, it is simply:
Sum(TOTAL <Month> Value)
What you can do is:
Temp_1
Load
Date,
Month(Date) as Month,
Day(Month) as Day,
Value
Resident <Table> Order by Date DESC;
next you can use Peek function to see if the month's differentiate and if the day is different, and create a new column
Best regards,
Dick
I need this in the load script, because I'll create an apply map with this table, related to this third column
Actually, what I need is a new dimension (created in load script), not a simple column by set analysis.
Hi,
Try this:
Datos:
LOAD * INLINE [
Date, Value
01/07/2015, 423
10/07/2015, 567
15/07/2015, 678
20/07/2015, 45
31/07/2015, 678
01/08/2015, 4576
10/08/2015, 657
14/08/2015, 432
21/08/2015, 6789
27/08/2015, 213
31/08/2015, 3467
];
Mapping:
MAPPING LOAD
Month(Date) as Month,
Sum(Value) as ValueAggr
Resident Datos
group by Month(Date);
Datos2:
LOAD Date,
Month(Date) as Month,
ApplyMap('Mapping',Month(Date)) as ValueAggr,
Value
Resident Datos;
Drop table Datos;
Regards,
Marcelo Hernandes wrote:
I need this in the load script, because I'll create an apply map with this table, related to this third column
Are you sure that is a good idea? It will limit the ability of the users to slice and dice the data. It is simple enough to calculate in the front end.
Values:
LOAD Date,
Value,
Month(Date) as Month
;
LOAD * INLINE [
Date, Value
07/01/2015, 423
07/10/2015, 567
07/15/2015, 678
07/20/2015, 45
07/31/2015, 678
08/01/2015, 4576
08/10/2015, 657
08/14/2015, 4132
08/21/2015, 6789
08/27/2015, 213
08/31/2015, 3467
];
JOIN
LOAD Month as Month,
Max(Date) as Date,
1 as IsMaxDate
RESIDENT Values
GROUP BY Month;
LEFT JOIN
LOAD Month,
Value as LasDayValue
RESIDENT Values
WHERE IsMaxDate =1;
DROP Field IsMaxDate From Values
try this
Data:
LOAD date(MakeDate(2014)+IterNo()-1,'DD-MM-YYYY') as Date,
monthname(MakeDate(2014)+IterNo()-1) as MonthName,
Ceil(Rand()*100+1000) as Value
AutoGenerate(1)
While IterNo()-1 <= Today()-MakeDate(2014);
Left Join
LOAD Distinct MonthName ,
max(Date) as Date,
1 as MaxDateFlag
Resident Data
Group by MonthName;
Left Join
LOAD Distinct MonthName,
sum(Value) as MaxValue
Resident Data
where MaxDateFlag=1
Group by MonthName;
Thanks, but the ValueAggr gives the month's sum values, not the last day value of each month:
What I expect is to see is the value 678 in all July days. And 3467 in all August days.
Ps: here, the data format is DD/MM/YYYY