Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have column called Year and another column called Month.
I want to concatenate these and make Year_Month field.
so it will be like 2015_Jan, 2015_Feb, 2015_March .........
thank you in advance!
Hi David,
Do you mean like this?
...
LOAD
Year,
Month,
Year &'_'& Month as Year_Month,
...
assuming that your Year and Month fields are in the correct format to begin with.
Jonathan
Try (assuming MonthField field textual values)
LOAD YearField,
MonthField,
MonthName(Makedate(YearField, Month(Date#(MonthField, 'MMM')) )) as Year_Month,
....
MonthName should return something like 'Mar 2016' as a QV dual value, as you should use for all calendar fields.
edit:
Having said that you should create duals from text input values, I already missed the MonthField, so you should do it like this when MonthField shows text like Jan, Feb, Mar, ... and these correspond to the MonthNames defined in script or OS:
LOAD *,
MonthName(Makedate(YearField, MonthField)) as Year_Month;
LOAD YearField,
Month(Date#(MonthField,'MMM')) as MonthField
...
Date(MakeDate(Year, Month), 'YYYY_MMM')
Dear David,
We use Ampersand Symbol (&) as a concatenation operator in QlikView.
Table1:
LOAD *, Year & '_' & Month as Year_Month Inline [
Product, Sales, Month, Year
A, 100, Feb, 2016
B, 200, Mar, 2016
];
String Concatenation:
'abc' & 'xyz' returns 'abcxyz'
Kind regards,
Ishfaque Ahmed
HI,
Try like this
LOAD
Year,
Month,
Date(MakeDate(Year, Month(Date#('Feb', 'MMM'))) , 'MMM_YYYY') AS MonthYear,
'
'
'
FROM DataSource;
If you arrive MonthYear field like this then you can sort it easily.
Regards,
jagan.
There are two possible ways,
1. if your month is like 'Jan', 'Feb' etc. then
Load
Year,
Month,
Year&'_'&Month as Year_Month
From x;
2. if your month is like 1,2,...12 then
Load
Year,
Month,
Year&'_'&pick(month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
from x;