Validate data with different allowed formats

[Michiel_QV_Fan]

I have a column accountID which contains valid values in different formats and invalid values in (for now 1 format). I want to validate based on rules.

Valid accountID represenations are:

  020          (a 0 is added with the num function to create 0020)

1050

8010

12350

07830

80150

etc.

Invalid accountID representation is:

01050, 08010

(this is the same value as the Valid accountID representation but with a preceding 0.)

I do not know in which data file (*) (multiple) accountID is formatted as valid of invalid.

The accountID will be linked to second table, related to each individual data file (*). This table has the correct value for accountID, for instance 01050 has (the valid) value 1050 in the second table.

I tested the pick and wildmatch function from rwunderlich‌ and that works for the invalid accountID where I added a ? to the other table but this cannot be used not when the 5 characters accountID is loaded......

How could I validate the format in this case?

Thanks

[gardenierbi]

Maybe this can help you : http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

[rwunderlich]

Have you tried using text() around everything? Then you could test with exists:

LOAD text(accountid) as ValidAccount from ...'

LOAD accountid, exists(ValidAccount, text(accountId)) as IsValidAccount from transactiions.

-Rob

[Michiel_QV_Fan]

Sander, usefull function but my accountID only contains numbers. 3, 4 or 5 digits are all ok. But 5 digits can be valid as well as invalid, depending on the content of the second table.

[Michiel_QV_Fan]

Rob,

Thanks for the suggestion.

To elaborate more on the question;

In the table with the valid accountID format I have for instance:

1050

8011

And in the table with invalid data:

01050

08011

I need to match 01050 with 1050. That's why I used the $(vMapExpr(accountID)) from your cookbook.

But the next load I have valid: 80150 with match 80150.

I load all these tables in 1 load statement with a loop to process all tables.

I can imagine it's not posible to solve this because I'm only working with numbers. In that case I have to figure out a work around.

[gardenierbi]

Can you upload a sample file?

[Michiel_QV_Fan]

No, not to disclose data .....

[Michiel_QV_Fan · Accepted solution]

Solution found.

Sander, Rob

I created a solution. Easier than I first thoughed but this works.

My solution:

First I load the table that has the Valid values.

I calculate the max length of that string and store that in variable length.

AccountID_length:

Load

  max(len(accountID)) as accountID_length

Resident [my table];

let length = peek('accountID_length', 0, 'AccountID_length');

Next I load the transaction with the Valid and/or Invalid values but I add

right(accountID, $(length)) as accountID

to that load. Preceding Invalid characters (numbers) are eliminated this way.

The downside of this is that as long as the Invalid values don't have a suffix added in the transaction this will work.