data validation script commands

[gino2780]

Hello,

i'm still rather new to qlik sense. So my question(s) for the attached spreadsheet is the following:

We want to establish a data validation app that validates our input data (mostly excel-sheets) according to the rules we set.

For example, one rule says that a certain field (column) shall only have cell-data with 7 digits (like customer ID's). With the data validation app we want to identify inconsistent data entries (e.g. having a customer ID with 8 digits).

I'm simply looking for the correct syntax or script command to realise this request.

[pamaxeed · Accepted solution]

Hi Vincenzo,

you jsut can do it in the script in a very simple way:

For example:

Table:

Load if(len(Kundenummer)>7, 'ERROR', 'OK') as Status,

   *

From Dummy.xls;

Hope it helps,

Patric

[ogster1974]

You can do that in excel easy enough using the following formula in an adjacent cell =IIF(LEN(C2)>7,"ERROR","OK")

Regards

Andy

[gino2780]

Hello Patric,

that was what i've been looking for. Thank you!

How would be the script command for combining several logical expression for e.g. the rule: show me cells that are >7 and <7? So everything that hasn't got 7 numerical digits can be identified (even blank fields or zero) via Status.

Regards,

Giacinto

[pamaxeed]

You can filter it with a where condition like;

Anomalia:

Load *

From Dummy.xls

Where len(Kundenummer)>7 AND len(Kundenummer)<7;

So all records which has some anomalias are returned.

Hope it helps

[gino2780]

Tried it with the command lines:

As you can see a library error shows up so that it can't read the data source.

That's curious, because i've used the same library command with your prior script command and it worked without error:

[pamaxeed]

You have to use the where condition below the from clause, like my example:

Load *

From Dummy.xls

Where len(Kundenummer)>7 AND len(Kundenummer)<7;

[gino2780]

I can't tell why this didn't work:

But i figured out that the following command lines did the job:

Load if(len(Kundennummer)<>7, 'ERROR', 'OK') as Status,

   *

FROM [lib://DQM/20150925_Auszug DQM-Sicht.xls]

(biff, embedded labels, table is [owssvr (9)$]);

It worked like: show me ERROR, if a cell has more or less than 7 digits. Almost like show me ERROR,  if a cell is unequal 7 digits. Is there a dedicated syntax command for unequal? Like =! 7 or =/= 7? Or ist it simply <>?

Edit2:

To make it even more simple:

How is the Syntax command for: Show 'ERROR', if a cell has not 7 digits or is blank?

[Anonymous]

can you tell hwo to write an script for validating the input data.

validation need to be done -  no of columns , sheet name , column name