Mapping from a SubField

[#1]

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

                              A

                              B

               Category Auth

                             A

                             B

Where I would wish for it to result as

Rec No 1 Category Paperwork

                             A

               Category Auth

                             B

Can anyone Please help me on this

Thanks a mill

Anne

[hic · Accepted solution]

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

[#3]

Thanks Henric

I believe I understand now, the above works perfectly,

Thanks

A

[#4]

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 ??

[hic]

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

[#6]

Hi Henric

Thanks again,

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

Anne