Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Extract year from dates in different columns

Hello All,

I have data in the below format

ProdJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20
A    1,914.98    1,090.24    1,123.97       1,344.77    1,590.58       1,326.06    1,549.463892.5161182.2092500.2234251.656    
B  39,685.56  41,327.88  60,680.73    74,768.95  27,229.35    74,025.02  53,041.0849282.3283063.5146598.5744360.68    
C        916.22        187.57        218.87  230,005.14- 34,506.59  936,901.63    6,510.7461608.5720293.8328567.4720724.35    
D                -                  -      1,008.60          495.44          66.65            72.82          69.4470.098510127.0340    
E    6,703.18    3,850.78    8,743.23    54,011.93    8,763.32       7,698.97    7,340.376380.8685570.67617125.535027.22    
F  26,837.36  23,210.16  17,339.80    20,502.18  12,983.80    12,727.47    9,915.4918592.0511051.6315139.3913309.72    

 

 

Is there a way to extract years from the dates into a new Year field?  so I could have 2019 and 2020 in a listbox  and on choosing one of them, the value of the month for that year appears? if I clicked 2020 it would only show me the values for January to March 2020 and so on. I've attempted to do a crosstable first of all but got stuck afterwards. Help will be very much appreciated. 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

 

Data:
CrossTable(YearMonth,Value)
LOAD
    Prod,
    "Jan-19",
    "Feb-19",
    "Mar-19",
    "Apr-19",
    "May-19",
    "Jun-19",
    "Jul-19",
    "Aug-19",
    "Sep-19",
    "Oct-19",
    "Nov-19",
    "Dec-19",
    "Jan-20",
    "Feb-20",
    "Mar-20"
FROM [lib://TEST]
(html, utf8, embedded labels, table is @1);

Final:
Load *,
     year(Date#(YearMonth,'MMM-YY')) as Year,
     month(Date#(YearMonth,'MMM-YY')) as Month,
     date(Date#(YearMonth,'MMM-YY'),'MMM-YY') as MonthYear
Resident Data;

Drop Table Data;

Drop Field YearMonth;

 

View solution in original post

4 Replies
Highlighted
MVP
MVP

 

Data:
CrossTable(YearMonth,Value)
LOAD
    Prod,
    "Jan-19",
    "Feb-19",
    "Mar-19",
    "Apr-19",
    "May-19",
    "Jun-19",
    "Jul-19",
    "Aug-19",
    "Sep-19",
    "Oct-19",
    "Nov-19",
    "Dec-19",
    "Jan-20",
    "Feb-20",
    "Mar-20"
FROM [lib://TEST]
(html, utf8, embedded labels, table is @1);

Final:
Load *,
     year(Date#(YearMonth,'MMM-YY')) as Year,
     month(Date#(YearMonth,'MMM-YY')) as Month,
     date(Date#(YearMonth,'MMM-YY'),'MMM-YY') as MonthYear
Resident Data;

Drop Table Data;

Drop Field YearMonth;

 

View solution in original post

Highlighted
Digital Support
Digital Support

Joe, I know Kush pretty much gave you what you need I think, but I just wanted to share the following Design Blog post in case you wanted to read through that to understand things a bit better.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

I am also going to give you the main Design Blog ULR link in case you want to file that away for future, as this area has hundreds of mostly how-to posts from our experts, so it should prove useful to you.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Oh, if Kush's solution does work for you, do not forget to return to things and use the Accept as Solution button on his post to give him credit for the help and to let the other Community Members know this worked.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Contributor II
Contributor II

Thank you Kush!

Highlighted
Contributor II
Contributor II

Thank you very much