Help with Nulling multiple fields for the same transaction date
(REPOST - ACCIDENTALLY POSTED IN THE WRONG THREAD).
I have a table containing a list of clients by company, branch, client number and cover number grouped together and named "Cover". The tables contains types of transactions made by each "Cover" and the date these transactions were made.
What I'm looking to do is null all transactions for the year for that specific cover if a certain transaction type was made.
Cover - 5ST-BRIA0007-0015445 has a Transaction Type "C" and "R" on 30/04/2013.
Because there is a transaction of "C" on that date, I would like all other transactions for this cover on that date/ or year (in this example "R") to be nulled.
The reason for this is that transaction "C" stands for cancellation of a policy, which means all other transactions for that policy in that year will now be void and should not be counted when working out the total premium of policies.
I have a feeling that some sort of mapping will be involved but at the moment i'm at a loss.
Any help would be greatly appreciated.
I've attached an image of the table for anyone willing to have a look.
1. In the script you create a table that distinct loads only the data where Transaction Type is "C" and sort it on Date field in ascending order. i.e. :
Temp: Load distinct ([Date field] & [Cover field]) as Key, Date field, Cover field resident (your table that holds all data) where Transaction Type='C' order by Date field asc;
This table would then hold only those dates & covers where Transaction Type is "C".
2. Again, load another table doing a resident from original table with all data, and then use the lookup() function in it to check if any current value for Cover falls in the Temp table, then replace it's respective Transaction Type value to NULL(). i.e.:
//if datefield value returned by lookup function falls in same year as current tables datefield value then replace TransactionType field value to NULL(), else use existing TransactionType value. if(InYear(Date field,lookup('Date field','Key',[Date field] & [Cover field],'Temp'),0)=-1,NULL(),TransactionType) as TransactionType,
3. Then do calculations in your pivot table as usual.
Read through the lookup() & InYear() functions in the QlikView Reference Manual.
If you can provide some data, it could be worked out. Or may be you can try the same at your end using above method.
While your code has been very useful in nulling any transaction type within the year if the transaction code "C" was present, what it also does is null the most recent transaction of any "Cover", regardless of its transaction type. This means i'm left with a large amount of null values where I shouldn't, on top of all the transactions that have been nulled because a "C" transaction was made in that year.
I've attached an image highlighting what has been said above.
I'm unable to include the data due to size of the data and privacy reasons.
While it partly does what I'm looking for, again in some covers, transactions are being unnecessarily nulled.
I've used the code you provided without any alterations which does indeed nullany transaction type in the year if the transaction "C" is made, however, for some reason it nulls some other transactions too.
I read your code and understand what it should be doing, but for my set of data it doesnt seem to work 100%