Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Henric
I believe I understand now, the above works perfectly,
Thanks
A
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 ??
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
Hi Henric
Thanks again,
works perfect, I;ve split the load and all my calcs are correct , really appreciate your help
Anne