Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Maybe this can help you : http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/
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
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.
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.
Can you upload a sample file?
No, not to disclose data .....
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.