Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

script help with 2 data types

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

8 Replies
Anonymous
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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)

ClaimNumberLostIfText thread178169.jpg

Note that statement IIF doesn't exist in QlikView.

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

Peter

Not applicable
Author

Thanks I try that

Not applicable
Author

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
Specialist III
Specialist III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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)

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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;