topic Mapping from a SubField in QlikView

Mapping from a SubField

Mon, 26 Mar 2012 08:51:25 GMT

Hi Guys,

I am loading from an Excel doc that in a column Rejection Reasons has , in some instances multiple reason , see sample below :

Rec No	Customer	Rejection Reasons
1	Anne	A,B,
2	John	A,
3	Fred	B,C,
4	Nuala	B,C,D,
5	Tracy	B,
6	Steven	C,

I have it loading as [Rejection Reasons],

I also wish to able to identify number of instances of each as ReasonSubs, so for example

LOAD RecID,

trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

resident INPUT;

if(RejectionReason =B,Count RejectionReason) and this would give a result of 1

if(ReasonSubs =B,Count ReasonSubs) and this would give a result of 4

all of the above works fine, my issue is I wish to categorise each of the rejection reasons

So I have a map "RejectMap"

Reason	Category
A	Paperwork
B	Auth
C	Security
D	Fire

When I apply the map :

applyMap('RejectMap',trim(upper(subfield([Rejection Reasons], ','))),'Not catgorised yet') as RCMTCats

When There is just one reason - it woprks fine

But

When there are multiple reasons, Qlikview applys every ReasonSub within the rejection Reason into each categories appropriate -- So for example -

Rec No 1 Category Paperwork

Category Auth

Where I would wish for it to result as

Rec No 1 Category Paperwork

Category Auth

Can anyone Please help me on this

Thanks a mill

Anne

Mapping from a SubField

hic — Mon, 26 Mar 2012 09:17:02 GMT

The subfield function loops over the same record and picks out all subfields from a string, thus generating several records. If you have two subfield calls within the same Load, it will generate the (unlinked) cartesian product between the two result sets. And I think that that is waht happens here - you have one subfield call to create ReasonSubs and a second one to create RCMTCats.

Try a preceding Load instead - so that the definition of RCMTCats is based on ReasonSubs:

RejectMap:

Mapping Load * inline

[Reason Category

A Paperwork

B Auth

C Security

D Fire] (txt, delimiter is '\t');

Input:

Load *,

applyMap('RejectMap',ReasonSubs,'Not catgorised yet') as RCMTCats;

Load *,

Len(keepchar([Rejection Reasons],',')) as NoOfReasons,

trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

inline

[Rec No Customer Rejection Reasons

1 Anne A,B,

2 John A,

3 Fred B,C,

4 Nuala B,C,D,

5 Tracy B,

6 Steven C,] (txt, delimiter is '\t');

/HIC

Mapping from a SubField

Mon, 26 Mar 2012 12:20:07 GMT

Thanks Henric

I believe I understand now, the above works perfectly,

Thanks

Mapping from a SubField

Mon, 26 Mar 2012 12:58:27 GMT

Hi Henric

May I ask A related question,

I had previously been counting the volume of customers by

'1' as Number,

but now if a customer has just say 4 reasons , it counts that customer 4 times,

I have a Rec() as RecID which does seem to be logging correctly

So I had wanted to go

Count(rec()) as Number,

This is not working ,

Any suggestions please ??

Mapping from a SubField

hic — Mon, 26 Mar 2012 13:32:07 GMT

There are several ways to do this. One could be to use the [Rec No] that you have in the original table.

count([Rec No]) will evaluate to 4 for that customer, whereas

count(distinct [Rec No]) will evaluate to 1.

Another way is to keep the data in two tables. Then you can count CustomerID from one table and RejectID from a second:

Customers:

Load

[Rec No] as CustomerID,

Customer,

Len(keepchar([Rejection Reasons],',')) as NoOfReasons,

[Rejection Reasons]

From .....

Rejects:

Load CustomerID,

RecNo() as RejectID,

applyMap('RejectMap',ReasonSubs,'Not catgorised yet') as RCMTCats;

Load

CustomerID,

trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

resident Customers;

/HIC

Mapping from a SubField

Mon, 26 Mar 2012 14:31:52 GMT

Hi Henric

Thanks again,

works perfect, I;ve split the load and all my calcs are correct , really appreciate your help

Anne