Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Hopefully someone can help me in this. I have a file of data and I need to be able to count how many fields do not match on a clients order. For example :-
Key | Client | Buyer\Seller | Fruit | Coffee | Milk | Biscuit |
1234 | Client A | Buyer | Apple |
|
| hobnob |
1234 | Client B | Seller | Pear |
| Skimmed | custard cream |
4567 | Client C | Buyer |
| Latte |
|
|
4567 | Client D | Seller |
|
| Full Fat |
|
There will always be 2 clients only to a key and one will always be the buyer and one will always be the seller but again I stress they will both have the same ‘key’ number to identify that it is the same order. If we look at Key 1234 I would want a count of 1 for the ‘Fruit’ Field as they do not have the same fruit, a count of ‘0’ for the coffee as neither have ordered coffee, a count of 1 for the milk as client B ordered Skimmed but Client A forgot to and again a count of 1 for biscuit because they both choose different biscuits. I would imagine I would need to treat each field separately and rename it for the total to go under as whilst I want to check each field line by line I then want a total at the end for each field. So in the above table the end result should be :-
Fruit 1
Coffee 1
Milk 2
Biscuit 1
Something else that should be taken into account is that the keys in the table may not always be together ie I could have this order :-
Key | Client | Buyer\Seller | Fruit | Coffee | Milk | Biscuit |
1234 | Client A | Buyer | Apple |
|
| hobnob |
4567 | Client C | Buyer |
| Latte |
|
|
4567 | Client D | Seller |
|
| Full Fat |
|
1234 | Client B | Seller | Pear |
| Skimmed | custard cream |
So it needs to take into account the key on each take
Is anyone able to assist in this?
T1:
Load * Inline
[
Key,Client,Buyer\Seller,Fruit,Coffee,Milk,Biscuit
1234,Client B,Seller,Pear, ,Skimmed,custard cream
4567,Client C,Buyer, ,Latte, ,
4567,Client D,Seller, , ,Full Fat,
1234,Client A,Buyer,Apple, , ,hobnob
];
NoConcatenate Load
Key,Client,Buyer\Seller,Fruit,Coffee,Milk,Biscuit,
If(Key=Peek(Key),1,0) as Flag,
If(Key<>Peek(Key),0,If(Len(Trim(Fruit))=0 AND Len(Trim(Peek(Fruit)))=0,0,If(Len(Trim(Fruit))>0 Or WildMatch(Fruit,Peek(Fruit)) ,1,0))) as IsFruit,
If(Key<>Peek(Key),0,If(Len(Trim(Coffee))=0 AND Len(Trim(Peek(Coffee)))=0,0,If(Len(Trim(Coffee))>0 Or WildMatch(Coffee,Peek(Coffee)) ,1,0))) as IsCoffee,
If(Key<>Peek(Key),0,If(Len(Trim(Biscuit))=0 AND Len(Trim(Peek(Biscuit)))=0,0,If(Len(Trim(Biscuit))>0 Or WildMatch(Biscuit,Peek(Biscuit)) ,1,0))) as IsBiscuit,
If(Key<>Peek(Key),0,If(Len(Trim(Milk))=0 AND Len(Trim(Peek(Milk)))=0,0,If(Len(Trim(Milk))>0 Or WildMatch(Milk,Peek(Milk)) ,1,0))) as IsMilk
Resident
T1
Order By Key,Client
;
Drop Table T1;
thanks Sasidhar i had to change it around a little bit but the main part the 'Peek' feature seems to do the trick appreciate your time in suggesting this
actually apologies Sasidhar what you have provided doesnt quite work. It counts when there is 2 different values for the same key but it also needs to could when there is a value vs an empty cell for the same key. Can anyone assist further? thanks
sorry to add the reason why Sasidhar's answer does not work is because it only works based on me knowing what the potential input to these fields is. In my live data the input is freeformat so i need an option where i am not deriving known values for it to check, thanks
please provide the cases where the logic didnt work or upload a sample app with scrambled data