Last 7 digits of an IDnumber Check

[kaygee28]

Good day All

I am currently trying to verify the last 7 digits of an IDnumber since some of the data in our database was imported incorrectly the last seven digits of the IDnumber hence is only zeros for instance 8806170000000

hence if qlikview comes acrross an IDnumber like this then it should be regarded as being invalid, hence I tried this code but it doesn't give me the results I want because qlikview is regarding the zeros as a number 0 hence the last six zeros are ignored.

Is there a workaround on this?

    if (len(IDNumber)=13,'Valid Idnumber',

    if (right(IDNumber,7)<>0000000,'Valid Idnumber','Invalid Idnumber')) as [IDnumber Check]

Thanks in advance

[Anil_Babu_Samineni]

Would you be able to provide the application

[kaygee28]

Hi Marcus

id	email	Full Name	populationgroup	IDNumber
20636192	jedavids@hotmail.com	John Elias Davids	NULL	4810030000000
20636203	mjkahn@yahoo.com	Michael Jeffrey Kahn	NULL	4810090000000
19928403	CARINAOELOFSEN@YAHOO.COM	Emmanuel Oelofsen	NULL	7104195129088
19928409	KASHIF.GOGAN@GMAIL.COM	Mohammad Kashif Gogan	NULL	8101245883085
19928411	CLINTONR@ARCTEC.CO.ZA	Clinton Ian Rawlinson	NULL	7903075102085

The first two Idnumbers are incorrect while the last three are corrrect

[HirisH_V7]

Hi

Check this Both,

Data:
LOAD *,
If(Right(ID,7)='0000000' or Len(ID)>13,'InValid','Valid') as Status1,
If(Right(ID,7)='0000000' ,'InValid',If( Len(ID)>13,'InValid','Valid')) as Status2
INLINE [
    ID
    8806170000000
    8806170000001
    8806170000002
    8806170000003
    98806170000003
];

You can use both conditions,

HTH,

PFA,

Hirish

[marcus_sommer]

I think the others are right and your first condition will be always true. Therefore you need to combine the lenght- and the last 7 chars-check like in the other examples.

- Marcus

[jonathandienst · Accepted solution]

How about this:

If (Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000', 'Invalid', 'Valid')) as [IDnumber Check]

[sunny_talwar]

May be try this:

Table:

LOAD id,

    email,

    [Full Name],

    populationgroup,

    IDNumber,

    Num(Right(IDNumber, 7)),

    If(Num(Right(IDNumber, 7)) = 0, 'Invalid Idnumber',

    If(Len(IDNumber)=13, 'Valid Idnumber')) as [IDnumber Check]

FROM

[https://community.qlik.com/thread/231977]

(html, codepage is 1252, embedded labels, table is @1);

[effinty2112]

Hi Kagiso,

          What about:

if(Frac(IDNumber/10000000) = 0, 'Valid','Invalid')

Good Luck

Andrew

[kaygee28]

Thanks everyone all your solutions workded, I highly appreciate all the help I received, this was simple enough

If (Len(IDNumber) <> 13 Or Right(IDNumber, 7) = '0000000', 'Invalid', 'Valid')) as [IDnumber Check]

[sonysree88]

Hi,

If you want to ignore last 7 zeros from your ID filed Can you please use the below expression where you want to use.

PurgeChar(IDNumber,'0')

Regards

Sonysre88