Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Nulling multiple fields for the same transaction date

(REPOST - ACCIDENTALLY POSTED IN THE WRONG THREAD).

Hi,

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.

For example:

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.

Kind regards,

Tony

12 Replies
somenathroy
Creator III
Creator III

Hi,

You may use the following expression:

sum(if(SubStringCount(Aggr(NODISTINCT Concat( Trans_Type,'-'),Cover,Renewal_Date),'C')>0,0,Premium))

Regards,

Som

Not applicable
Author

Hi Tony,
            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. :

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.

Hope this helps. Thanks.

Not applicable
Author

Hi Som,

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.

Tony

QlikView x64 - [CUsersuserDocumentsTEST.jpg

somenathroy
Creator III
Creator III

Please find the attachement.

Not applicable
Author

Hi Som,

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.

Tony

somenathroy
Creator III
Creator III

Hi,

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.

Regards,

Som

Not applicable
Author

Som,

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%

somenathroy
Creator III
Creator III

Can you please confirm that all the 'C' value in Trans_Type field is in upper case and also include the values of

Trans_Type field.

Regards,

Som

Gysbert_Wassenaar

Pretty much as Som said...

Income:

load *, AutoNumber(Cover&'|'&Renewal_Date) as Key from income.qvd (qvd)

where Trans_Type <>'C';

left join

load distinct AutoNumber(Cover&'|'&Renewal_Date) as Key, Trans_Type as Cancellation

from income.qvd (qvd)

where Trans_Type='C';

And then change your sum expression to:

sum({$-<Cancellation={'C'}>}Premium)

Alternatively you could do another load to throw out all the cancelled records:

Result:

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.


talk is cheap, supply exceeds demand