topic Re: Finding all fields (All at once) with null values while loading from an excel sheet. in QlikView

Finding all fields (All at once) with null values while loading from an excel sheet.

Wed, 25 Nov 2020 16:16:04 GMT

Waiting for a response since long:

This question is Not Answered.(Mark as assumed answered)

deepikasakorey 07-Apr-2017 06:58

Hello Everyone,

I have a csv file which has some fields with missing values and I want to identify which all fields have missing values. I can also use as stored procedure to get the same content as of the excel file.

Can anyone suggest how I an achieve this using either CSV file or stored procedure.

I started with the following code using stored procedure.

OLEDB connection;

load *;

SQL ****** EXEC Stored procedure syntax*************;

But in the above syntax all null values disappear from the fields.

Thanks in advance.

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

swuehl — Mon, 24 Apr 2017 22:03:50 GMT

Can you post a sample of such csv file and your expected result?

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Anil_Babu_Samineni — Tue, 25 Apr 2017 01:43:37 GMT

How is your stored procedure looks like, CAn you send full information maybe

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Tue, 25 Apr 2017 07:57:36 GMT

Hi Stefan,

I would like to correct myself . I am reading the data from a stored procedure. The Data has some fields with missing values and I have to identify those fields.

Example:

Input

StudID Name Grade

1 XY A

2 ZU missing value

YD B

Expected Output:

FieldName MissingValue_Flag

StudID 1

Name 0

Grade 1

Thanks in advance,

Deepika

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Tue, 25 Apr 2017 07:58:29 GMT

Heyllo Anil

Thanks for your reply. I could send only this much part of my procedure as an example.

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

swuehl — Tue, 25 Apr 2017 08:43:24 GMT

It depends what you want to do with this information further down the road.

For a good overview of your table meta data, you could store your table to a QVD.

Then use e.g. EasyQlik QViewer to show the meta data for that table, looks like

Column 'Null count' is what you are interested in, right?

You can also use the statistics box in QlikView to show the NullCount for a field (or use an expression with NullCount(FIELDNAME), but I think it's harder to get an overview over all fields at once.

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Tue, 25 Apr 2017 08:53:37 GMT

Hi Stefan.

I need to then compare the output with my QualityCheck criteria. Basically, first I want to check which fields have missing values in the data and then I have to check if they are supposed to have missing values from my criteria list .

So for example my criteria list looks something like this:

Criteria List:

FieldName MissingValue_Flag

StudID 0

Name 0

Grade 0

age 1

Expected Output List from data:

FieldName MissingValue_Flag

StudID 1

Name 0

Grade 1

age 1 (Additional field added to data to show this field is accepted with missing values)

Then compare both and thus identify which fields shouldnot have missing value. for eg, here all the fields except age can have missing values.

Hope this example is more clear.

I cant use Viewer in this context then.

Thanks

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Tue, 25 Apr 2017 08:55:46 GMT

Sorry for the typo.

I meant ....

all the Fields except age should have non-missing values and age can be missing or non-missing.

Thanks

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Wed, 26 Apr 2017 08:27:40 GMT

Does anybody have a solution to my question? Please share.

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

swuehl — Wed, 26 Apr 2017 08:45:04 GMT

You can use NullCount(Field) to check for Nulls in an aggregated table load of your input table, if you want to dynamically get all field names, you can iterate over the field names in your table using system functions, something like this (replace TEST table name with your resident table name):

For i = 1 to NoOfFields('TEST')

Let vScript = 'NullCount(' & FieldName($(i),'TEST') & ') as Nulls, ' & chr(39) & FieldName($(i),'TEST') & chr(39) & 'as Field';

Nulls:

LOAD $(vScript)

Resident TEST;

Next i

Re: Finding all fields (All at once) with null values while loading from an excel sheet.

Tue, 02 May 2017 20:55:28 GMT

Hi Stephan,

Thanks for the script .I tried it and it did give me what I wanted. But I also need to identify and display the rows of the fields with missing values.

Do you have any tip ?

for example:

Test1:

Student_name student_id age gender

A A1 15

B B1 F

C 20 M

So now from your formula I can identify which fields have missing values.

Here fields Student_id , age and gender have missing values.

Now I have rule_book which is as follows:

fieldname Should_have_missing_values

Student_name 1 ( cant have)

Student_id 1 (cant have)

age 0(may or may not have missing values)

gender 1(cant have missing values)

Now I have compare this rule book to the data(Test1) to identify which fields dont follow the rules and display rows where the rules arent followed.

for eg. student_id should not have null values. and the row is

C .... 20.... M etc

Can you help here?

Thanks,

Deepika