Automate field calculation for all fields in data model

[prayner]

Problem

In Qlik data load editor, I am trying to calculate the null count and non-null count for all fields in the data model and produce a table that contains those summary statistics. How can I repeat the following code for all fields (>40 fields) in my data model so that I don't end up with an unnecessarily long script.

Section of the code to automate for all fields:

```

,NullCount(field1) as null_count_field1
,Count(field1) - NullCount(field1) as non_null_count_field1

,NullCount(field2) as null_count_field2
,Count(field2) - NullCount(field1) as non_null_count_field2

...

,NullCount(fieldN) as null_count_fieldN
,Count(fieldN) - NullCount(field1) as non_null_count_fieldN

```

Reprex script:

// Create sample data
LET vToday = Today();

sample_data:
LOAD * INLINE [
batch_date, field1, field2, field3
$(vToday), 10, 9, 2
$(vToday), 12, 11, 3
$(vToday), , ,
$(vToday)-1, 9, 8, 1
$(vToday)-1, 11, 10, 2
$(vToday)-1, , 9,
];

// Calculate null and non-null counts
field_counts:
LOAD
batch_date
,NullCount(field1) as null_count_field1
,Count(field1) - NullCount(field1) as non_null_count_field1
,NullCount(field2) as null_count_field2
,Count(field2) - NullCount(field2) as non_null_count_field2
,NullCount(field3) as null_count_field3
,Count(field3) - NullCount(field3) as non_null_count_field3
RESIDENT sample_data
GROUP BY batch_date;

// Use CrossTable to reshape the data
counts_xtable:
CrossTable(field_name_t, value, 1)
LOAD *
RESIDENT field_counts;
DROP TABLE field_counts;

// Process the reshaped data
counts:
LOAD
batch_date
,subfield(field_name_t, '_count_', -1) as field_name
,subfield(field_name_t, '_count_', 1) as count_type
,value
RESIDENT counts_xtable;
DROP TABLE counts_xtable;

// Display the results
counts_display:
LOAD
batch_date,
field_name,
count_type,
value
RESIDENT counts;

// Clean up
DROP TABLE sample_data;
DROP TABLE counts;

[Kushal_Chawda · Accepted solution]

@prayner  try below

Fields:
Load * Inline [
Junk ];

let vToday = date(Today(),'DD-MM-YYYY');

// Loop through fields of the table. Change the table name as per your data
for i=1 to NoOfFields('Data')

let vFieldName = FieldName($(i),'Data');

let vCountRows = NoOfRows('Data');

// load non null count values within loop
Concatenate(Fields)
Load  date(Today(),'DD-MM-YYYY') as batch_date,
     '$(vFieldName)' as field_name,
     count([$(vFieldName)]) as value,
     'non_null_values' as count_type
Resident Data;

Next

// Substract previous count from No of rows. Count only ignores actual null values.
Concatenate(Fields)
Load  batch_date,
      field_name,
     '$(vCountRows)' - value as value,
     'null_values' as count_type
Resident Fields;

Drop Field Junk;

// store daywise QVD
Store Fields into lib://DataFiles/TableName_Fields_Metadata_$(vToday).qvd(qvd);

Drop Table Fields;

// Load all Metadata QVDs with *
Metadata:
LOAD *
FROM lib://DataFiles/TableName_Fields_Metadata_*.qvd(qvd);

[prayner]

Validated, thanks again