topic Re: script help with 2 data types in QlikView

script help with 2 data types

Thu, 27 Aug 2015 14:34:14 GMT

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

Re: script help with 2 data types

Anonymous — Thu, 27 Aug 2015 15:05:42 GMT

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

Re: script help with 2 data types

Peter_Cammaert — Thu, 27 Aug 2015 15:20:20 GMT

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

Re: script help with 2 data types

Thu, 27 Aug 2015 15:25:11 GMT

Thanks I try that

Re: script help with 2 data types

Thu, 27 Aug 2015 15:27:32 GMT

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

Re: script help with 2 data types

ramoncova06 — Thu, 27 Aug 2015 15:29:35 GMT

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

Re: script help with 2 data types

Peter_Cammaert — Thu, 27 Aug 2015 15:45:35 GMT

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)

Re: script help with 2 data types

Thu, 27 Aug 2015 16:02:52 GMT

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

Re: script help with 2 data types

petter — Thu, 27 Aug 2015 16:07:24 GMT

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;