topic Re: Pull Only the Best Matching Number in QlikView

Pull Only the Best Matching Number

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

I have a set of part numbers (MahleCram and Trimmed) that match each other, but some numbers are better matches than others. How closely they match is based off the parity; ranging from AA to AI. AA, AB, AC etc being the best matches and AD, AF etc have more differences.

I want to pull into a column only the best matching number and make it so the second best matching number is not even listed; so there are no duplicates.

An example is Mahlecram number 72112. It ties out to "Trimmed" number 410 (which is an AB parity) and 72515 (AG parity.) I only want to show the Mahlecram tying out to 410 because it is the best matching number and not even show 72515.

Please help! And check out my attachment....

Thanks

Re: Pull Only the Best Matching Number

sunny_talwar — Thu, 07 Jun 2018 00:41:46 GMT

You want this to be done in the script to completely remove 72515 or do you want to do this in a chart?

Re: Pull Only the Best Matching Number

oliveton — Thu, 07 Jun 2018 11:34:49 GMT

Let's try in the script.

Thanks

Re: Pull Only the Best Matching Number

sunny_talwar — Thu, 07 Jun 2018 11:46:55 GMT

Try this

CustFile:

LOAD Distinct

// Part,

Trimmed,

Text(PurgeChar(PurgeChar(Trimmed, ' ') , '-')) as PartCleanCustomer,

Text(PurgeChar(PurgeChar(Trimmed, ' '), '-')) &'FEL-PRO INC.' as %PartClean,

[Part Desc 1] as CustDescription//,

// [PO Cost]

FROM

(ooxml, embedded labels, table is FP);

Left Join (CustFile)

//IntrchFP:

LOAD Distinct

// F1,

ITEMA as MahleCram,

PNPUBA as MahlePublished,

// VEHTYA,

// ENGMAK,

// CACMCD,

CMNAME,

CACMPC,

Text(PurgeChar(PurgeChar( CACMPC, ' '), '-')) &CMNAME as %PartClean,

// CMTYPE,

CAPAR as Parity,

ABCA as PopCode,

DESCA//,

// CAMSTQ,

// CACMPQ,

// CMCMCD,

// CLASA,

// CACRIN,

// CACOMM

FROM

(biff, embedded labels, table is INTRCHFP$)

//Where Parity = 'AB'

;

Right Join (CustFile)

LOAD MahleCram,

MinString(Parity) as Parity

Resident CustFile

Group By MahleCram;

Re: Pull Only the Best Matching Number

oliveton — Thu, 07 Jun 2018 12:12:02 GMT

This works!

The only thing I noticed is for numbers such as MahleCram 1634 which ties out to 7183PT and 7183S; the number that stays is 7183PT which has an AG parity. The number I would want to stay is 7183S which has the better parity of AD. (AA and AB being the best and going down the line all the way to AI.)

Do you by chance have a way of doing this?

Thanks so much

Re: Pull Only the Best Matching Number

sunny_talwar — Thu, 07 Jun 2018 12:20:05 GMT

This is the app image below is from the app before my changes... and I see 1634 is only associated with one Trimmed and that is 7183PT

Re: Pull Only the Best Matching Number

oliveton — Thu, 07 Jun 2018 13:11:28 GMT

I should have taken a screen shot.....because now that's all that appears for me too, which is strange.