First Qlikview-Project: Change (Add, Modify, Delete) Qlikview-Table-Entries with daily delta-files

[Anonymous]

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.

How could I do this with Qlikview?

Best regards,
Angus MacGyver

[laksyelugoti]

Hi Angus MacGyver,

Welcome to Qlik world..

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';

Good luck

Laks