Change and delete fieldnames, based on another table

[qlikmark1990]

I tried to program a filter in my QlikView application to manipulate the data while loading from the data source. The source can't be adjusted and there are some known issues with the data which are described in a separate Excel file. The content of this Excel sheet is:

sheet	veld	actie	waarde-in	waarde-uit
NAW	zoeknaam	delete	010100WE-490	null
NAW	zoeknaam	delete	011214TE-30	null
NAW	zoeknaam	delete	021214EN-31	null
NAW	zoeknaam	delete	031214TE-27	null
NAW	zoeknaam	delete	170600TE-580	null
NAW	zoeknaam	wijzig	010160EN-32-0	010160EN-32
NAW	zoeknaam	wijzig	011166VE-168-	011166VE-168
NAW	zoeknaam	wijzig	050573VE-793-	050573VE-793

sheet is the file name that needs to be adjusted.

veld is fieldname to be adjusted

actie is action (delete = delete, wijzig = change)

waarde-in is the orginal value and waarde-uit is the correct value.

If the field 'zoeknaam' in the table 'NAV' has the values 010100WE-490, 011214TE-30, 021214EN-31, 031214TE-27 or 170600TE-580, the complete row needs to be deleted. If the field 'zoeknaam' has the values 010160EN-32-0, 011166VE-168- or 050573VE-793-, the need to be adjusted as described in 'waarde-uit'

So far, my code is :

Aanpassingen_data:

LOAD sheet, veld, actie, [waarde-in], [waarde-uit]

FROM [Aanpassingen data.xlsx] (ooxml, embedded labels, table is Blad1);

TestNAV:

LOAD zoeknaam as zoeknaamTest, geslacht as geslachtTest, H_PC as H_PCTest, geboortedatum as geboortedatumTest, land as landTest]

FROM [$(vExcelPath)171220 NAW.xls] (biff, embedded labels, table is [first sheet$])

Where not exists ([waarde-in], zoeknaam) and Match(actie, 'delete');

But I recieve the error:

Field not found - <actie>

TestNAV:

LOAD zoeknaam as zoeknaamTest, geslacht as geslachtTest, H_PC as H_PCTest, geboortedatum as geboortedatumTest, land as landTest]

FROM (biff, embedded labels, table is [first sheet$])

Where not exists ([waarde-in], zoeknaam) and Match(actie, 'delete')

When I remove the Match() function I don't receive an error but all of the records described in 'Aanpassingen_data' are deleted and I just want to delete the first 5 records and change the other three.

[marcus_sommer]

I think I would try something like this:

MapAanpassingen_data:

Mapping LOAD [waarde-in], [waarde-uit]

FROM [Aanpassingen data.xlsx] (ooxml, embedded labels, table is Blad1)

where actie <> 'delete';

TestNAV:

LOAD

     applymap('MapAanpassingen_data', zoeknaam, zoeknaam) as zoeknaamTest,

     geslacht as geslachtTest,

     H_PC as H_PCTest,

     geboortedatum as geboortedatumTest, land as landTest]

FROM [$(vExcelPath)171220 NAW.xls] (biff, embedded labels, table is [first sheet$])

Where len(applymap('MapAanpassingen_data', zoeknaam, null())) >= 1;

- Marcus

[qlikmark1990]

Thank you for this helpful answer, the problem that I have is that I only get three records in TestNAV:

geboortedatumTest	geslachtTest	H_PCTest	landTest]	zoeknaamTest
01-01-1960	man	HA0 2HH	Verenigd Koninkrijk	010160EN-32
01-11-1966	vrouw	3812 DC	Nederland	011166VE-168
05-05-1973	man	7603 GA	Nederland	050573VE-793

What I need to have is all records, except the 5 ones that are in error (delete). And the 3 ones shown above.

[marcus_sommer]

If I look on your example I thought this was the aim - 8 records overall and 5 ones with 'delete' and therefore 3 records remain or was something else meant?

- Marcus

[qlikmark1990]

All records except the 5 ones was the idea and the three records changed.