topic Re: How to handle missing fields in Connectivity & Data Prep

How to handle missing fields

k_holt69 — Tue, 11 Jun 2024 11:19:54 GMT

Hi All
I have a QVD that stores surveys results that I need to create a table from. The problem is the data stored in the QVD is only kept for a rolling 2 weeks and if a survey question has not been answered within that 2 week period, the field then it is not stored in the QVD but may become available again in the future if someone answers the question. This can happen for any question/field.

Is there a way to recognise that 1 or more fields may not be available and "force" an entry
EG
Load
Q1 as Q1,
Q2 as Q2,
Q3 as Q3,
Q4 as Q4,
Q5 as Q5
From QVD

Q2 is missing so "NA" as Q2
Q3 is missing so "NA" as Q3

any help appreciated

Re: How to handle missing fields

rwunderlich — Tue, 11 Jun 2024 13:00:58 GMT

I assume you are doing "LOAD *" from the QVD as the available fields will vary. Here's a script pattern that will add the missing fields. I've simulated the QVD load with an Inline load of table "Data".

Data: // The QVD 
LOAD * Inline [
Q1, Q4, Q5
a, b, c
]
;
TempLoadedFields:
LOAD
  FieldName(RecNo(), 'Data') as Field
AutoGenerate NoOfFields('Data')
;
MissingFields:
LOAD
Concat('''NA'' as ' & ExpectedField, ', ') as Missing;
;
// The expected fields
LOAD * Inline [ 
ExpectedField
Q1
Q2
Q3
Q4
Q5
]
Where not Exists(Field, ExpectedField)
;
Let vMissing = Peek('Missing');
Concatenate (Data) // Add missing fields to table
LOAD $(vMissing) AutoGenerate 1;  
Drop Table TempLoadedFields, MissingFields;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: How to handle missing fields

marcus_sommer — Tue, 11 Jun 2024 15:10:41 GMT

You may avoid this issue by resolving the crosstable-structures which have often more disadvantages as benefits. Here is a good explanation to the idea: The Crosstable Load - Qlik Community - 1468083