Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do i add an additional column to this load in line table? It will be called 'Year' and will take the year from the 'DocDate' field. In this example the result would be 2018
LOAD * INLINE [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast, Year
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53
You can use the Precedent Load:
Load *,
Year(DocDate) as YearFromDocDate;
Load * Inline [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53];
Try it.
Regards,
Sorin.
Load *,
Year(Timestamp#(DocDate,'DD/MM/YYYY hh:mm:ss.fff')) as YearFromDocDate;
Load * Inline [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53];
Load *,
Year(DocDate) as Year;
Load * Inline [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53];
Thanks, this worked. Do you know how I would display the months as names rather than numbers e.g jan, feb etc.
Load *,
Year(DocDate) as YearFromDocDate,
Month(DocDate) as MonthFromDocDate;
Load * Inline [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53];
Load *,
Year(Timestamp#(DocDate,'DD/MM/YYYY hh:mm:ss.fff')) as YearFromDocDate,
Date(Floor(Timestamp#(DocDate,'DD/MM/YYYY hh:mm:ss.fff')) ,'MMM') as MonthFromDocDate;
Load * Inline [
AcctDoc, DocType, DocDate, PostingDate, Reference, AcctCd, MaterialNo, SoldToParty, ActValue, BudgetValue, NBSub, NewBusiness, Year(null), Period, Forecast
0000512000, RV, , 02/01/2018 00:00:00.000, , , 0098/000012, , ,622049.53, , , , , 622049.53];