Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stevelord
Valued Contributor

zipcode is zipcode when I specify user, but zipcode is date when I don't?

Hi Qlikcommunity, this one is confounding me but maybe a qlikview/sql ninja can help solve it.  Maybe 1-2% of our zipcode values are coming out as dates on the Qlikview table objects.  The field type is string, but we are stripping hyphens on import.  The weird thing is, if I just add 'where user id = 12345' to the end of the query for one of the users with a bogus zipcode on the table, the correct zipcode value comes out on the table.  But when I leave that piece off, and search the user id on the table box, that user id's zip code value is a date that isn't related to anything about them that I can see. The bogus date is 1981, their birthday is in 1960, and the data did not exist prior to 2000, so not sure where 1981 is coming from and why 29707 is there instead when I narrow the query.  Please help.  Thanks!

Zipcode value comes out as 29707 on the test qlikview dashboard

UserAttributes:
LOAD AttributeName,
AttributeType,
AttributeValue,
UserId;
SQL SELECT *
FROM "$(DbLHMTSchema)".dbo."lkup_user_attribute"
WITH (nolock)
JOIN "$(DbLHMTSchema)".dbo."user_attribute" (nolock) on "$(DbLHMTSchema)".dbo."lkup_user_attribute"."AttributeId" = "$(DbLHMTSchema)".dbo."user_attribute"."AttributeId"
WHERE AttributeValue is not null and UserId=12345;

Zipcode comes out as a date for user id 12345 on the test qlikview dashboard

UserAttributes:
LOAD AttributeName,
AttributeType,
AttributeValue,
UserId;
SQL SELECT *
FROM "$(DbLHMTSchema)".dbo."lkup_user_attribute"
WITH (nolock)
JOIN "$(DbLHMTSchema)".dbo."user_attribute" (nolock) on "$(DbLHMTSchema)".dbo."lkup_user_attribute"."AttributeId" = "$(DbLHMTSchema)".dbo."user_attribute"."AttributeId"
WHERE AttributeValue is not null;


EDIT:

Interesting fun fact… 29707 does translate to 5/1/1981 when you type it in excel and change the number format to date…  no clue why QV does that or how it’s happening sporadically. (i.e. why aren’t ALL the zip codes dates, and why does this one show correctly when I specify the user in the query…)


1 Solution

Accepted Solutions
MVP
MVP

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

I don't think I get a solution for the root cause, maybe you want to contact QT support.

You can try fixing the value representation by using some formatting functions.

Try

LOAD AttributeName,

    AttributeType,

    if(isnum(AttributeValue), num(AttributeValue), AttributeValue) as AttributeValue,

    UserId;

...

or something else...

8 Replies
Employee
Employee

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

Would you mind selecting top 10 * from each table lkup_user_attribute and user_attribute and posting here?

MVP
MVP

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

29707 when interpreted as QV date is Jan 5th, 1981. At least this could explain where the 1981 might come from.

stevelord
Valued Contributor

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

I noticed this on a hunch in excel, so it tells me that at least the correct zipcode value is under their somewhere and not some random date from a sql query gone sideways.  Problem is, this is happening to some but not all zipcodes in a table box, and I am not specifying format anywhere.  And it doesn't happen if I specify the user id in a where statement on the sql query part.

To the other person, I did a top 1000 then setup a table box for lkup and the attributes, then filtered to zipcodes.  The bogus zip codes aren't in this sample set, but it gives a view of the table structure at least.  (The tables were millions of rows and there were some blanks early on or no zipcode values in the first 10 of the one table.)  Per above comment, it seems more like a formatting issue of some kind than a table structure/query flaw.  The AttributeId 3 is the one sometimes giving date (maybe 2% of the time) and sometimes zipcode in the original example, but not sure if others might be doing it too.  Also we're not actually stripping hyphens because I see those.

user.attribute.lkup table fields and some values

  

AttributeIdAttributeNameAttributeTypeClientId
3Zipcodestring
228Zip Codestring3
448Zip Codestring19
247ZIP CODEstring2
277Zipstring4
159WorkZipfloat8

user.attributes table fields and some values.

    

AttributeIdAttributeValueBlobAttributeValueSelfReportedUserAttributeIDUserId
31934113560855121
37763013560874122
38002713148974074
37734613148987075
159
228
247
277
448
stevelord
Valued Contributor

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

Also SQL developer colleague of mine tells me to use convert(varchar, AttributeValue) as AttributeValue on the one line, but Qlikview tells me convert is not a valid function.  There are a few SQL functions I can't use in qlikview.  I might figure out a workaround for this one along the lines of formatting to number any zipcodes greater than 10 characters  long.  The longest normal zipcode would be 'xxxxx-xxxx', and the dates I see look like 'xx\xx\xxxx xx:xx:xx'.

We have an employee id field that likes to put varying amounts of leading 0s in front of some employee ids and not others with the same apparent randomnes. It's a string field as well and no safe workaround I can see, but leading 0s seems to be the only buggy phenomenon there at least... (some people you search 123, and if that doesn't work you start trying 0123 00123 000123 until one works.  Or bomb the column with convert to number in excel where safe to do so.)

MVP
MVP

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

Are you using the convert() in the SQL SELECT or LOAD part of the script?

I think QV should not complain when it's part of the SQL SELECT statement.

stevelord
Valued Contributor

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

Error message went away, but I still get the zipcode as a date for the particular user in the table box.  Issue is still only on a small percentage of the users' zipcodes. But it looks like all of the 29707 zipcodes are affected, and some other zipcodes.

UserAttributes:

LOAD AttributeName,

    AttributeType,

    AttributeValue,

    UserId;

SQL SELECT AttributeName,

    AttributeType,

convert(varchar,AttributeValue) as AttributeValue,

    UserId

FROM "$(DbLHMTSchema)".dbo."lkup_user_attribute"

WITH (nolock)

JOIN "$(DbLHMTSchema)".dbo."user_attribute" (nolock) on "$(DbLHMTSchema)".dbo."lkup_user_attribute"."AttributeId" = "$(DbLHMTSchema)".dbo."user_attribute"."AttributeId"

WHERE AttributeValue is not null;

If I go like the script below, I get a regular zipcode for the user in my table box though:

UserAttributes:

LOAD AttributeName,

    AttributeType,

    AttributeValue,

    UserId;

SQL SELECT AttributeName,

    AttributeType,

convert(varchar,AttributeValue) as AttributeValue,

    UserId

FROM "$(DbLHMTSchema)".dbo."lkup_user_attribute"

WITH (nolock)

JOIN "$(DbLHMTSchema)".dbo."user_attribute" (nolock) on "$(DbLHMTSchema)".dbo."lkup_user_attribute"."AttributeId" = "$(DbLHMTSchema)".dbo."user_attribute"."AttributeId"

WHERE AttributeValue is not null and UserId=12345;

MVP
MVP

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

I don't think I get a solution for the root cause, maybe you want to contact QT support.

You can try fixing the value representation by using some formatting functions.

Try

LOAD AttributeName,

    AttributeType,

    if(isnum(AttributeValue), num(AttributeValue), AttributeValue) as AttributeValue,

    UserId;

...

or something else...

stevelord
Valued Contributor

Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?

This forced the zipcode to number, but also did the same to other attributes that were dates (like the date of birth).

Interestingly the phenomenon returned and persisted when I added a little and wildmatch(AttributeName, 'Zip*') to try just num()ing the zipcodes and not the dateofbirths etcetera.  And even persisted after storing into qvd and then reloading and trying to format it just where attributename is Zipcode.  (The gremlin was laughing at me now.)

There is another transformation qvw that has script like if(wildmatch(AttributeName,'Zip*') and AttributeValue, AttributeValue) as [Zip Code] to make a Zip Code field (along with the other parts of the address).  I loaded that qvd and put if(not wildmatch([Zip Code], '*-*'), num([Zip Code]), [Zip Code]) as [Zip Code] (And so the gremlin was squished.)

That worked.

I wonder if the size of the data is something and maybe QV is processing something incompletely or getting tired... something weird.. We got a solution and I'll run away with it now.  I will try a variation of this on the more important employee id field too- to see if I can knock some mystery leading 0s off those...

Community Browser