topic concatenating columns in QlikView

concatenating columns

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: concatenating columns

jonopitchford — Wed, 09 Mar 2016 23:22:42 GMT

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

Re: concatenating columns

swuehl — Wed, 09 Mar 2016 23:29:28 GMT

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.

Get the Dates Right

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

...

Re: concatenating columns

maxgro — Wed, 09 Mar 2016 23:49:02 GMT

Date(MakeDate(Year, Month), 'YYYY_MMM')

Re: concatenating columns

engishfaque — Thu, 10 Mar 2016 03:42:53 GMT

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

Re: concatenating columns

jagan — Thu, 10 Mar 2016 03:49:00 GMT

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.

Re: concatenating columns

amayuresh — Thu, 10 Mar 2016 04:19:30 GMT

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;