topic Re: Counting Fields that do not Match in QlikView

Counting Fields that do not Match

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Counting Fields that do not Match

sasiparupudi1 — Tue, 17 Apr 2018 09:40:08 GMT

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

Order By Key,Client

;

Drop Table T1;

Re: Counting Fields that do not Match

Anonymous — Tue, 17 Apr 2018 09:58:04 GMT

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

Re: Counting Fields that do not Match

Anonymous — Wed, 25 Apr 2018 10:31:01 GMT

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

Re: Counting Fields that do not Match

Anonymous — Wed, 25 Apr 2018 10:37:14 GMT

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

Re: Counting Fields that do not Match

sasiparupudi1 — Wed, 25 Apr 2018 11:04:18 GMT

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

Scrambling Data