topic Automate field calculation for all fields in data model in App Development

Automate field calculation for all fields in data model

prayner — Wed, 02 Oct 2024 13:40:52 GMT

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;

Re: Automate field calculation for all fields in data model

Kushal_Chawda — Wed, 02 Oct 2024 15:24:23 GMT

@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);

Re: Automate field calculation for all fields in data model

prayner — Fri, 18 Oct 2024 11:59:29 GMT

Validated, thanks again