Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
All records except the 5 ones was the idea and the three records changed.