Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to check a number of barcodes if they are correctly matched with the correct product types. So for e.g. a barcode that begins with 111 is a hat , 123 is a shoe etc. Some products have been wrongly labeled. so 123 is put down as a hat instead of a shoe. So I want to filter this out. This is what I have tried so far and I will appreciate some suggestions. thanks in advance
Count({<Barcode = {'*^111*'}, [Product Type] = {'hat'}>} 1)
I also tried
=Count(if(Barcode='*^111*' and [Product Type]= 'hat',1,
if((Barcode) = '*^123*' and [Product Type] = 'Shoe', 1,
if((Barcode) = '*^124*' and [Product Type] = 'Gloves', 1,
if((Barcode) = '*^125*' and [Product Type] = 'dress', 1,
if((Barcode) = '*^126*' and [Product Type] = 'Shirt', 1,
if((Barcode) = '*^127*' and [Product Type] = 'Tops', 1,0
)
)
)
)
)
)
Would you be able to elaborate what exactly are you trying to do and where with a help of any example?
Building number | Product type | Barcode |
000045630 | Hat | 1110000001679 |
000045669 | Shoe | 1110000012345 |
000045634 | Dress | 1240000000001 |
000045634 | Socks | 1250000000025 |
000045634 | Shirt | 1260000034035 |
000045712 | Shoe | 1230000000444 |
000045731 | Hat | 1110000000799 |
in the table, you can see that the product type 'Shoe' appears as with barcode prefix - 111 and 123(1110000012345 and 1230000000444) This is inconsistent and so it is wrong.
I want to be able to check the barcode against the product type. so all hats have 111 and shoes are 123 ... etc
So
=Count(if(Barcode='*^111*' and [Product Type]= 'hat',1,
if((Barcode) = '*^123*' and [Product Type] = 'Shoe', 1,
if((Barcode) = '*^124*' and [Product Type] = 'Dress', 1,
if((Barcode) = '*^125*' and [Product Type] = 'Socks', 1,
if((Barcode) = '*^126*' and [Product Type] = 'Shirt', 1,0
)
)
)
)
)
does this make sense?
It does, but I am confused as to what is ^ used for? Do you may be need this
=Sum(If(
(WildMatch(Barcode, '111*') and [Product Type] = 'hat') or
(WildMatch(Barcode, '123*') and [Product Type] = 'Shoe') or
(WildMatch(Barcode, '124*') and [Product Type] = 'Dress') or
(WildMatch(Barcode, '125*') and [Product Type] = 'Socks') or
(WildMatch(Barcode, '126*') and [Product Type] = 'Shirt'), 1, 0))
If you notice, I removed the initial * also in the WildMatch function above, because you are checking the first 3 characters... adding * in the beginning will make it a wild card search anywhere in the string....
I used this ^ to define all barcode beginning with 111, 123 etc.
Since I am trying to ensure that all the barcodes that begin with 111, 123, 124... are hat, shoe, Dress... respectively(basically if it begins with 111 and its a shirt, then I want to count it as an error and flag it but if it begins with 111 and is a hat, then it is okay not to count as it is not an error),
I have now tried the following also
= Count(DISTINCT Aggr(If
(Barcode = '*^111*' and [Product Type] = 'hat') or
(Barcode = '*^123*' and [Product Type] = 'shoe') or
(Barcode = '*^124*' and [Product Type] = 'Dress'), 0, 1))
It however doesnt return the correct value I am looking for. I am not sure if i have missed something. Please have a look?
Did you ever got a chance to try this?
=Sum(If(
(WildMatch(Barcode, '111*') and [Product Type] = 'hat') or
(WildMatch(Barcode, '123*') and [Product Type] = 'Shoe') or
(WildMatch(Barcode, '124*') and [Product Type] = 'Dress') or
(WildMatch(Barcode, '125*') and [Product Type] = 'Socks') or
(WildMatch(Barcode, '126*') and [Product Type] = 'Shirt'), 1, 0))
yes it didnt work as expected
Would you be able to share a sample to help you better?