First Qlikview-Project: Change (Add, Modify, Delete) Qlikview-Table-Entries with daily delta-files
Hi all,
I`m completly new to QlikView and unfortunately I`m also not a traines database-professional;so I beg your pardon if my question / first question might be to obvious 😉
I have a base table as tabulated text-file containing the following columns Modification Flag, Record Key, Data 1, Data 2, etc.
I started my project by just loading that base table:
Table_Base:
LOAD [MODIFICATION FLAG],
[RECORD KEY],
[DATA 1],
[DATA 2]
FROM
[C:\Users\Windows.User\Documents\DATA\DATA_V3_FULL_20181123.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq);
On each day of the week I now get so called "daily-delta" files in the same format. I now want to update the Qlikview-Base-Table according to this "daily-delta-files".
The daily-delta-files could have the following values in field "Modification Flag": A = Add, D = Delete and M =Modified. The "Record Key" present is a unique key consisting of 12 alphanummeric characters.
The Qlikview-Base-Table now should be updated (Add, Delete, Modify) according to the "Modification Flag" for the matching "Record Key" specified in the daily-delta file.
Side notes: - Rows which will be added (ModiFlag = A) will have a new unique Record Key. - Especially for the first daily-delta-file delivered right after creation of the base table it will be possible that there are rows mentioned in the daily-delta-file which should be deleted (ModiFlag = D) but which are not present in the base table. This should not throw an error.
I understand that delta files will have new records to add, existing records to modify records, existing records with a identifier field "FLAG", you want to add / modify / delete records to Table_base based on the FLAG field in the delta file. Here is the sample code helps you to get the idea.
// Load delta file first, and create a new key for records to be modified or deleted. Delta_file: Load *, If(FLAG = 'D' or FLAG = 'M', RECORD_KEY ) as Exclude_Record_key // identify M and D records From delta-file ;
// Load Table_base without records to be deleted or modified. Table_base: noconcatenate First Load based table Table_Base without delete records Load * from Table_base where NOT exists(Exclude_Record_key,Record_key);
// Add A or M records from Delta file to Table_base concatenate(Table_base) Load * resident Delta_file WHERE FLAG = 'A' or FLAG = 'M';
