topic Load an excel file with conditions in QlikView

Load an excel file with conditions

bellesol — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm trying to load an excel file but I need to add a few conditions.

Sometimes the client adds corrupted data to the excel file, and the goal is to load to the model only the correct data that meet the conditions, and all the corrupted data to load into a separate QVD file.

**an example of the excel file with dummy data is attached.

The conditions:

"Doc. Year" - has to be a number and to include 4 numbers (cannot be empty)

"Doc. Month" - has to be a number and to include 1 to 2 numbers. (cannot be empty)

"Doc. Number" - has to be a date in the format - "DD-MM-YYYY" (cannot be empty)

"Doc. Type" - has to be a string

"Item Code" - has to be a number and if the row is empty then "Item Cat." has to be empty too.

"Item Cat." - has to be a number and if the row is empty then "Item Code" has to be empty too.

Also, If it possible, when loading the corrupted data into a separate qvd file, to add the reason why the data hasn't been loaded,

for example, records that don't have a year will be loaded into a qvd file with the reason "Missing Year" and so on...

Thanks in advance 🙂

Re: Load an excel file with conditions

sergio0592 — Mon, 13 Jan 2020 13:56:08 GMT

Hi,

You can use few Qlik functions:

Item N°1 : IsNum() and len()

N°2 : IsNum()

N°4: IsText()

Re: Load an excel file with conditions

Brett_Bleess — Tue, 11 Feb 2020 21:08:03 GMT

Did Sergio's post help with your issue? Do not forget to give him credit by using the Accept as Solution button if it did, and if you did something different, consider posting that and marking it.

I would suspect what you may want to look into is our new products from the Data Integration side of things, but I am not sure how much of this you have to do as to whether it would be worth that or not. You can reach out to your Qlik Account Manager to see if they can arrange a demo of those tools for you, but that is likely the best avenue I believe if you need something more robust.

https://www.qlik.com/us/products/data-integration-products

Regards,
Brett