Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
k_holt69
Contributor III
Contributor III

How to handle missing fields

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

Labels (1)
2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

marcus_sommer

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