Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
robmarr789
Contributor
Contributor

extract year from date column and add as new column - Qlikview

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

6 Replies
isorinrusu
Partner - Creator III
Partner - Creator III

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.

sasiparupudi1
Master III
Master III

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];

vishweshwarisun
Partner - Creator
Partner - Creator

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];

robmarr789
Contributor
Contributor
Author

Thanks, this worked. Do you know how I would display the months as names rather than numbers e.g jan, feb etc.

isorinrusu
Partner - Creator III
Partner - Creator III

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];

sasiparupudi1
Master III
Master III

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];