topic Re: Help with Nulling multiple fields for the same transaction date in QlikView

Help with Nulling multiple fields for the same transaction date

Mon, 04 Mar 2013 01:59:46 GMT

(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

Re: Help with Nulling multiple fields for the same transaction date

somenathroy — Mon, 04 Mar 2013 05:51:36 GMT

Hi,

You may use the following expression:

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

Regards,

Som

Re: Help with Nulling multiple fields for the same transaction date

Mon, 04 Mar 2013 07:50:46 GMT

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.

Re: Help with Nulling multiple fields for the same transaction date

Mon, 04 Mar 2013 22:55:36 GMT

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

Re: Help with Nulling multiple fields for the same transaction date

somenathroy — Tue, 05 Mar 2013 06:09:46 GMT

Please find the attachement.

Re: Help with Nulling multiple fields for the same transaction date

Tue, 05 Mar 2013 23:18:20 GMT

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

Re: Help with Nulling multiple fields for the same transaction date

somenathroy — Wed, 06 Mar 2013 10:25:46 GMT

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

Re: Help with Nulling multiple fields for the same transaction date

Wed, 06 Mar 2013 23:28:05 GMT

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%

Re: Help with Nulling multiple fields for the same transaction date

somenathroy — Thu, 07 Mar 2013 10:30:07 GMT

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

Re: Help with Nulling multiple fields for the same transaction date

Gysbert_Wassenaar — Thu, 07 Mar 2013 11:19:11 GMT

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.

Re: Help with Nulling multiple fields for the same transaction date

Thu, 07 Mar 2013 23:02:24 GMT

Hi Gysbert,

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.

Re: Help with Nulling multiple fields for the same transaction date

Thu, 07 Mar 2013 23:03:06 GMT

Hi Lara,

Appreciate the time you've spent trying to help me out.

Will give it a go and let you know how it works.

Tony.

Re: Help with Nulling multiple fields for the same transaction date

Thu, 07 Mar 2013 23:05:14 GMT

Som,

I confirm that all "c" values in Trans_Type field is in upper case.

Values of Trans_Type field are: A , C ,D, F, J, L, N, R, V, X.

Tony