How to validate the Excel sheets data in Qlikview

[Anonymous]

Hi,

I have been given a task to validate the excel data if it is in the correct format to update to qlikview server.

My questions are:

1.Qlikview should check the excel data if all the fields have the right format .For eg: if Date format is right and need to check if numbers are entered in the right fields not in the string field.

2.Qlikview should able to run the script and isolate the files and save it in a file  which needed correction and the remaining files can be uploaded.

3.Qlikview should send the mail or alert the user if some of the files are in wrong format.

pls someone can help me out .

Regards,

Vamsi.

[ThornOfCrowns]

How many rows of data are you looking at?

[Anonymous]

For an example ....My scenario is 100 rows of Excel data .Among 90 records are in correct format and 10 are in wrong format.So how qlikview helps me in the validation of data

[marcus_sommer]

If you want check the data quality you need at least two loads. The first to load the file and check such things like:

recno() with rowno()

isnum(Field)

len(Field)

substringcount(Field, AnyChar)

....

If there are any unexpected values you created a field/variables-value with an error-value and following loads could skip these files or rows. If you want inform the users about incorrect files within the script you could use some EXECUTE batches.

Easier could be to load all data in your app and have a field with correctData = 'Yes'/'No' and use email-alerts within the gui.

- Marcus

[Anonymous]

Thank you Marcus.I will try with your suggestions.

[Anonymous]

Hi marcus ,

I am unable to do at script level .Can you explain me how shall i start?

[marcus_sommer]

It will be rather impossible to handle all possible kinds of errors or invalide data but you will have some requirements which fields you have to check on which validity. I would suggest to you to start small with a few fields and checks for testing and if it runs you could extend it. Maybe like this:

t0:

Load *, if(isnum(field1) and len(field2) >= 8 and/or something, 'valid data', 'invalid data') as DataCheck From x;

If your amount of checks rather large then it would be better to use mapping or splitting the checks in several load-steps or similar instead to create many and endless if-loops.

Alltogether it's quite a lot of work - rather in hours or days as in minutes. Good luck.

- Marcus

[Anonymous]

Hi marcus,

Thanks for your reply.I have tried but  i am getting this error

Cannot open file '\\wwutilities.co.uk\home\CF02\Vamsi.Jonnagadda\Qlikview Documents\Qlikview Dev\FROM
[\\wwutilities.co.uk\home\CF02\Vamsi.Jonnagadda\Qlikview Documents\QlikExcel\Validation Check.xlsx]' The filename, directory name, or volume label syntax is incorrect.

Load *,if(isnum(Emp_Id)and Date(Emp_date, "DD.MM.YYYY" ),'valid data', 'invalid data') as datacheck from 
FROM
[\\wwutilities.co.uk\home\CF02\Vamsi.Jonnagadda\Qlikview Documents\QlikExcel\Validation Check.xlsx]
(ooxml, embedded labels, table is Sheet1)

pls help me

Regards,

Sharma.