topic mandatory column field in Qlik Automate

mandatory column field

inam6713 — Wed, 09 Oct 2024 12:00:32 GMT

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.

Re: mandatory column field

MK_QSL — Wed, 09 Oct 2024 18:44:50 GMT

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;