Thanks for attempting to help me out with this.
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've attached my file so you can see what i mean.
While your code works on the majority of the fields, some fields are being nulled even when the transacton "C" has not been made in that year.
Appreciate your help so far Som.
ACTIVE POLICY TEST.qvw 40.3 MB
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%
Pretty much as Som said...
load *, AutoNumber(Cover&'|'&Renewal_Date) as Key from income.qvd (qvd)
where Trans_Type <>'C';
load distinct AutoNumber(Cover&'|'&Renewal_Date) as Key, Trans_Type as Cancellation
from income.qvd (qvd)
And then change your sum expression to:
Alternatively you could do another load to throw out all the cancelled records:
Load * resident Income
where Cancellation <> 'C';
drop Table Income;
drop Field Cancellation;
If you're going this way you might want to store Income to a qvd file after the left join and load again from that qvd file. That might be faster than a resident load.
I've had a go your way and it comes back with the same result as Som's.
While it successfully nulls any transaction in a year if the transaction type "C" is made (where I want to be), it also nulls other random "Cover"'s which do NOT contain transaction type "C".
What we are trying to do is find the number of active policies we have. If the policy has been cancelled ("C") then all other transactions made for that policy needs to be nulled out as it is no longer active and we do not want that data to be included in the rest of our calculations.
Using your second method while may throw out all cancelled records, still leaves other transactions for the policy for that year which will skew our data.
Appreciate the time you spent trying to assist Gysbert.
I'd suggest the following method:
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. :
([Date field] & [Cover field]) as Key,
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.
Hope this helps. Thanks.