mandatory column field

[inam6713]

In QlikView, I have a column FILE_TYPE with two values: 'FIT' and 'GIT'. Sometimes 'GIT' is missing in the raw data, and sometimes 'FIT' is missing. I want to ensure that both fields are always present in my table. For instance, if SUB_BUSINESS is 'Domestic' and FILE_TYPE 'GIT' is not in the raw data (SEE BELOW SNAPSHOT), I want to include FILE_TYPE 'GIT'  in my table with all values showing as 0.

[MK_QSL]

You can create missing data in script. Something like below.

DATA:

Load *, SUB_BUSINESS & FILE_TYPE & [FILE DATE] as %KEY Inline

[

SUB_BUSINESS, FILE_TYPE, FILE DATE, Txn, Value, Pax

    Domestic, FIT, 08/10/2024,1, 11, 111

    International, FIT, 08/10/2024,2, 22, 222

    International, GIT, 08/10/2024,3, 33, 333    

];

NEW_DATA:

Load Distinct SUB_BUSINESS Resident DATA;

Join (NEW_DATA) Load Distinct FILE_TYPE Resident DATA;

Join (NEW_DATA) Load Distinct [FILE DATE] Resident DATA;

Concatenate(DATA)

Load *, SUB_BUSINESS & FILE_TYPE & [FILE DATE] as %KEY Where Not Exists(%KEY, SUB_BUSINESS & FILE_TYPE & [FILE DATE]);

LOAD * RESIDENT NEW_DATA;

DROP TABLE NEW_DATA;