topic Combing two number fields into a text field. in QlikView

Combing two number fields into a text field.

Tue, 02 Sep 2014 23:34:45 GMT

I need to combine two number fields together. I have two fields. One is Year and the other is Month.

During the load, I want to create a new field with these two fields.

Year = 2014 Month = 08

The new field will have a new value something like 2014-08

Year &”-“& Month Does not work.

Re: Combing two number fields into a text field.

phaneendra_kunc — Tue, 02 Sep 2014 23:36:06 GMT

Try single quotes...

Year &'-'& Month as YEAR_MONTH

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 00:03:45 GMT

Thanks for the reply,

My bad, typo I did have single qoutes.

The result value is 0

Re: Combing two number fields into a text field.

phaneendra_kunc — Wed, 03 Sep 2014 00:12:28 GMT

that is wired... it should work. Here all you are doing is concatenating two fields with a separator.

Year &'-'& Month as YEAR_MONTH

Can you check the number format of this dimension in Document properties -- > Number tab

Mark as "Mixed" .

If that doesn't resolve post a sample QVW

Re: Combing two number fields into a text field.

jagan — Wed, 03 Sep 2014 01:46:09 GMT

Hi Tom,

Try like this

LOAD

Text(Year & '-' & Month) AS YearMonth

FROM DataSource;

Now use this new field as dimension wherever you required.

Regards,

Jagan.

Re: Combing two number fields into a text field.

ecolomer — Wed, 03 Sep 2014 04:11:58 GMT

Try this:

Year &'-'& (num(Month, '00')) as Year_Month

or use the function:

MonthName(Date)

as xMonthYear,

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 06:15:42 GMT

How about

=Year & '-' & Month

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 16:59:41 GMT

I tried this Text(Year & '-' & Month) AS YearMonth

well a verision of it. Text(Year) & '-'& Text(Month) as YearMonthLink

It came up with a SQL error on load.

Re: Combing two number fields into a text field.

MarcoWedel — Wed, 03 Sep 2014 17:03:05 GMT

In order to get a real date field, try this expression

Date(MakeDate(Year, Month, 1), 'YYYY-MM') as MonthName

This way you can also use this field with date functions and in calculations like any other date field.

hope this helps

regards

Marco

Re: Combing two number fields into a text field.

maxgro — Wed, 03 Sep 2014 17:05:14 GMT

are you using qlik syntax in the qlik side of the script or in the sql side?

// start of qlik side

LOAD

.....,

.....

;

// end of qlik side

// start of sql side

SQL SELECT

....

from

....

;

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 17:24:43 GMT

Ok, maybe I am loading wrong. Here how my load statement looks.

CTW:

BUFFER (stale after 10 hours)

LOAd * ; SQL

Select

year,

month,

from datatable

How should I be loading?

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 18:17:38 GMT

What I have read, I have to use the sql load. I'm using an odbc to load.

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 18:21:58 GMT

When I tried this, I get an error saying SQL does not regconize MakeDate as a function.

Re: Combing two number fields into a text field.

stevedark — Wed, 03 Sep 2014 18:28:40 GMT

As Massimo says you need to do the concatenate in the preceding load, something like this:

LOAD

year & ' ' & month as YearMonth

;

SQL Select

year,

month,

from datatable

If you do that though the resultant value will be a string, and will not be ideal for date comparisons, this would be better still:

LOAD

Date(MakeDate(year, month), 'YYYY-MM') as YearMonth

;

SQL Select

year,

month,

from datatable

;

This way the resultant value will be a dual (i.e will have a numeric and text representation) and will sort correctly and be more efficient.

You also need to ensure that you always match the case of field names.

Hope that helps.

Steve

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 21:36:27 GMT

Thanks that works. I now have a second issue, well kind of the same issue.

I'm loading two tables with the new field. this will be my join between the two tables. The first table loads fine with the new field.

The second table errors out saying that saying that the Year field can't be found.

So only the 1st table loads with the new field. The second table does not load.

Both are coming from the same database.

Re: Combing two number fields into a text field.

Wed, 03 Sep 2014 21:41:11 GMT

Thanks Steve,

now I understand!

I want to use it as a joint between two tables. I can't use the document date to tie the table together. The tables need to be tie together on the month and year that the tables are downloaded from a bank website.

Re: Combing two number fields into a text field.

Thu, 04 Sep 2014 17:54:35 GMT

Thanks everyone for your help on my issue. I ended up putting the code on the SQL select statement. Both table now joins together on this new field.

CAST(t0.Year AS char(4)) + '-' +

case when len(t0.month) = 1

then '0' + CAST(t0.Month AS char(2))

else CAST(t0.Month AS char(2))

end as Yr_mth_link

Tom