Extract year from dates in different columns

[joejobs]

Hello All,

I have data in the below format

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
A	1,914.98	1,090.24	1,123.97	1,344.77	1,590.58	1,326.06	1,549.46	3892.516	1182.209	2500.223	4251.656
B	39,685.56	41,327.88	60,680.73	74,768.95	27,229.35	74,025.02	53,041.08	49282.32	83063.51	46598.57	44360.68
C	916.22	187.57	218.87	230,005.14	- 34,506.59	936,901.63	6,510.74	61608.57	20293.83	28567.47	20724.35
D	-	-	1,008.60	495.44	66.65	72.82	69.44	70.09851	0	127.034	0
E	6,703.18	3,850.78	8,743.23	54,011.93	8,763.32	7,698.97	7,340.37	6380.868	5570.676	17125.53	5027.22
F	26,837.36	23,210.16	17,339.80	20,502.18	12,983.80	12,727.47	9,915.49	18592.05	11051.63	15139.39	13309.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. 

[Kushal_Chawda · Accepted solution]

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;

[Brett_Bleess]

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

[joejobs]

Thank you Kush!

[joejobs]

Thank you very much