Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Mapping from a SubField

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 NoCustomer Rejection Reasons
1AnneA,B,
2JohnA,
3FredB,C,
4NualaB,C,D,
5TracyB,
6StevenC,


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"

ReasonCategory
APaperwork
BAuth
CSecurity
DFire

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

            

       

     

1 Solution

Accepted Solutions

Mapping from a SubField

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

5 Replies

Mapping from a SubField

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

Not applicable

Mapping from a SubField

Thanks Henric

I believe I understand now, the above works perfectly,

Thanks


A

Not applicable

Mapping from a SubField

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

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

Not applicable

Mapping from a SubField

Hi Henric

Thanks again,

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


Anne