8 Replies Latest reply: May 29, 2015 3:15 PM by Steve Lord RSS

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

    Steve Lord

      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…)

       


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

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

          • Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?
            Stefan Wühl

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

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

                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
                  • Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?
                    Steve Lord

                    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.)

                      • Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?
                        Stefan Wühl

                        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.

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

                            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;

                              • Re: zipcode is zipcode when I specify user, but zipcode is date when I don't?
                                Stefan Wühl

                                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...

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

                                    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...