Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
michielvandegoo
Valued Contributor

Validate data with different allowed formats

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

1 Solution

Accepted Solutions
michielvandegoo
Valued Contributor

Re: Validate data with different allowed formats

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.

7 Replies
gardenierbi
Contributor

Re: Validate data with different allowed formats

Re: Validate data with different allowed formats

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

michielvandegoo
Valued Contributor

Re: Validate data with different allowed formats

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.

michielvandegoo
Valued Contributor

Re: Validate data with different allowed formats

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
Contributor

Re: Validate data with different allowed formats

Can you upload a sample file?

michielvandegoo
Valued Contributor

Re: Validate data with different allowed formats

No, not to disclose data .....

michielvandegoo
Valued Contributor

Re: Validate data with different allowed formats

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.

Community Browser