Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Counting Fields that do not Match

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?

5 Replies
sasiparupudi1
Master III
Master III

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;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

sasiparupudi1
Master III
Master III

please provide the cases where the logic didnt work or upload a sample app with scrambled data

Scrambling Data