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

[t_moutault]

hi ,

try this

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

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

[kaygee28]

Hi Thomas,

Unfortunately that didn't work, I even tried this and it still doesn't,

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

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

[kaygee28]

[jpenuliar]

Wildmatch(IDNumber,'*0000000')

[kaygee28]

Hi jpenuliar

I get the same results for both of these scripts, so it cant be correct.

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

    if (Wildmatch(idnumber,'*0000000'),'Valid Idnumber','Invalid Idnumber')) as [IDnumber Check]

thats yours and

if (len(idnumber)=13,'Valid Idnumber','Invalid Idnumber')) as [IDnumber Check]

[marcus_sommer]

I suggest you provide a small example with a few inline-data with the typical valid- and invalid-values.

- Marcus

[trdandamudi]

The reason it will not work because:

Your first IF condition is already a 'Valid IDNumber" (if (len(IDNumber)=13,'Valid Idnumber'). Meaning the length will be always 13 and it will not go to the second IF condition. You can try as below and it will work:

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

[antoniotiman]

Hi,

Try

if(len(IDNumber)=13,if (right(IDNumber,7)<>0000000,'Valid Idnumber','Invalid Idnumber'),'Invalid Idnumber') as [IDnumber check]

[trdandamudi]

Even the below code will work:

Load IDNumber,

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