Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
New Contributor III

Re: script help with 2 data types

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

Highlighted

Re: script help with 2 data types

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

Highlighted
Not applicable

Re: script help with 2 data types

Thanks I try that

Highlighted
Not applicable

Re: script help with 2 data types

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

Highlighted
Valued Contributor III

Re: script help with 2 data types

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

Highlighted

Re: script help with 2 data types

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)

Highlighted
Not applicable

Re: script help with 2 data types

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

Highlighted
MVP
MVP

Re: script help with 2 data types

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;