topic Re: Change and delete fieldnames, based on another table in QlikView

Change and delete fieldnames, based on another table

qlikmark1990 — Tue, 30 Jan 2018 13:09:02 GMT

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.

Re: Change and delete fieldnames, based on another table

marcus_sommer — Tue, 30 Jan 2018 14:35:17 GMT

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

Re: Change and delete fieldnames, based on another table

qlikmark1990 — Tue, 30 Jan 2018 15:13:59 GMT

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.

Re: Change and delete fieldnames, based on another table

marcus_sommer — Tue, 30 Jan 2018 15:52:02 GMT

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

Re: Change and delete fieldnames, based on another table

qlikmark1990 — Tue, 30 Jan 2018 16:14:05 GMT

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