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

[#1]

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.

[swuehl]

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

[Anil_Babu_Samineni]

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

[#4]

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

             DY        

Expected Output:

FieldName  MissingValue_Flag

StudID           1

Name             0

Grade             1

Thanks in advance,

Deepika

[#5]

Heyllo Anil

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

[swuehl]

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.

[#7]

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

[#8]

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

[#9]

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

[swuehl]

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