script help with 2 data types

[#1]

Hello,

I have a column called "ClaimNumber" in an access database , and am running this query

SQL SELECT ClaimantName,

    ClaimNumber,

    DateOfInjury,

    GroupNumber,

    Year(DateOfInjury)&' - '&IIf(GroupNumber=0,ClaimNumber, GroupNumber)  as YearClaimNumber

FROM Claims;

which works in Access

The issue is when ran in the Qlikview script it ignores any ClaimNumbers that have letters in them  i.e. "WNSB100290"

How can I get qlikview to pick up these values that start with letters, convert them all to string maybe?  Thanks

[Anonymous]

Hi Brad, just use text(ClaimNumber) which will treat everything as a string. so Year(DateOfInjury)&' - '&IIf(GroupNumber=0,text(ClaimNumber), GroupNumber)  as YearClaimNumber.

[Peter_Cammaert]

That's weird and shouldn't really happen. I guess that something else must be wrong in either your data or your script.

If I enter this code in an empty test document:

Claims:

LOAD * INLINE [

ClaimantName, ClaimNumber, DateOfInjury, GroupNumber,

ABC, 1, 1/1/2013, 0

DEF, 2, 1/2/2013, 1

GHI, WNSB100290, 1/3/2014, 0

JKL, GRNL100123, 1/4/2015, 1

];

LOAD ClaimantName,

    ClaimNumber,

    DateOfInjury,

    GroupNumber,

    Year(DateOfInjury)&' - '&If(GroupNumber=0,ClaimNumber, GroupNumber)  as YearClaimNumber

RESIDENT Claims;

DROP Table Claims;

I get this output which AFAIK is correct (table box)

Note that statement IIF doesn't exist in QlikView.

Can you post a reduced example document with data to investigate?

Peter

[#4]

Thanks I try that

[#5]

Your right its odd, although this is an access table im hitting and my have something to do with it, the query pulls the proper data set in access , but leaves out the claimnumbers containing characters on the qlikview load, odd indeed

[ramoncova06]

might be an issue with the ODBC, as Peter stated if I do this at the load level it does work

[Peter_Cammaert]

To check where this is happening exactly, try a simple straightforward LOAD that stores the Access data in a resident table before you start manipulating data of creating new columns.. If string values already go missing at that point, you may have to tweak your access connection (or the simple LOAD statement)

[#8]

Yeah I tried exporting the access table to excel and importing that way , and it works fine, the issue is with access I guess, why would that happen in access?  weird

[petter]

If you precede the SQL SELECT with a LOAD you can prevent QlikView from trying to interpret ClaimNumber as a number by doing this:

LOAD
  ClaimantName,
  Text(ClaimNumber) AS ClaimNumber,
  DateOfInjury,
  GroupNumber,
  YearClaimNumber;
SQL SELECT
  ClaimantName,
    ClaimNumber,
    DateOfInjury,
    GroupNumber,
    Year(DateOfInjury) & ' - ' & IIf(GroupNumber=0,ClaimNumber, GroupNumber)  as YearClaimNumber
FROM Claims;