Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Concatenate date & num issue

Hello folks, Can anyone enlighten me as to why the following is happening? And a possible solution / workaround?
I wish to concatenate two tables based on Year field.
Year does not exist in tableA only date column ("date" format when right click in Excel, DD/MM/YYYY, e.g 01/01/2011)
TableB "general" format when right click in Excel. (e.g 2011)
When I perform the following script I receive strange results?
TableNamexxx:
Load
date (SalesTblShipDate,'YYYY') as ShipYearIDKey
resident Sales;
Concatenate (TableNamexxx)
TargetsTblYear as ShipYearIDKey
resident targets;
The results
ShipYearIDKey
2011
1905
The targets table is showing 2011. But when I perform the above action, Year from my second table (targets) is coming through at 1905??
Any clues why this is happening?
Many thanks in advance
1 Solution

Accepted Solutions
MVP
MVP

Concatenate date & num issue

Your year 2011 of the second table is interpreted as Date type's numerical representation, 2011 is the representation of 1905, July 3rd.

Try using

year(SalesTblShipDate) as ShipYearIDKey,

in your first load.

4 Replies
MVP
MVP

Concatenate date & num issue

Your year 2011 of the second table is interpreted as Date type's numerical representation, 2011 is the representation of 1905, July 3rd.

Try using

year(SalesTblShipDate) as ShipYearIDKey,

in your first load.

Not applicable

Concatenate date & num issue

Thank you swuehl.

We had thought that was the cause but hadn't thought to use the year function.

We do have pretty much the same issue with trying to extract month number from first load from date field.  Second table has month as single digit for months 1 to 9.  I've looked on help files and have only come around month(date) function but that only gives you returns a text string representing the month.  We getting similar incorrect error as we were getting on year.  So same issue.

What would suggest for similar case with month number?  Colleague was thinking using MID with ROUND combination?  Not tried mind you, just an idea.

MVP
MVP

Concatenate date & num issue

month() function has also a text and a numerical representation. Check out

=month(today())

vs.

=num(month(today()))

If you have a complete date in the first table and year, month, day in the second, it might be better to format both as dates, i.e. in the second table, you could create a Date using

makedate(TargetsTblYear ,TargetsTblMonth, TargetsTblDay) as DateField,

Not applicable

Concatenate date & num issue

Hi again swuehl.  Your sugguestion to resolve our month number has worked a treat thank you. Used in the end:

num(month(SalesTblShipDate)) as ShipMthNoIDKey

Many thanks.

Community Browser