Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
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

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.
Contributor II
Contributor II

Thank you Kush!

Contributor II
Contributor II

Thank you very much