topic Re: Extract year from dates in different columns in QlikView

Extract year from dates in different columns

joejobs — Sat, 16 Nov 2024 01:35:13 GMT

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.

Re: Extract year from dates in different columns

Kushal_Chawda — Mon, 06 Jan 2020 15:23:55 GMT

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;

Re: Extract year from dates in different columns

Brett_Bleess — Mon, 06 Jan 2020 20:17:16 GMT

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

Re: Extract year from dates in different columns

joejobs — Tue, 07 Jan 2020 16:19:10 GMT

Thank you Kush!

Re: Extract year from dates in different columns

joejobs — Tue, 07 Jan 2020 16:20:51 GMT

Thank you very much