(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.
You may use the following expression:
sum(if(SubStringCount(Aggr(NODISTINCT Concat( Trans_Type,'-'),Cover,Renewal_Date),'C')>0,0,Premium))
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.
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.
Actually I am using QV personal edition and unable to open your document. Can you pls. include some test data, thus can test the expression.
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%
Can you please confirm that all the 'C' value in Trans_Type field is in upper case and also include the values of
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.