Array validation!

[Bigmonki]

Hi All,

A Monday morning conundrum that I would like assistance with please!

I have two fields that I have to validate, one is a string of letters (the letters never change only the order in the string), the other is a number corresponding to the letter (the numbers never change, again only depending on where in the previous string the letter sits). The PRICECOD string has no delimiters!

 

So PRICECOD will always contain RLASTW but not always in that order

PRICENUM (1, 2, 3 etc.) will always contain a number that relates to the position of the Letter in the string

We have put together a Data array input table with what we think are all the permutations (attached), we think there are 720 permutations. 

Can you help me build the query that looks for the letter position in the PRICECOD string, then checks the correct number is in the relevant PRICENUM field?

[afv2688]

Hello @Bigmonki,

To be honest I'm a little lost and not sure if this is what you are looking for.

Hope it helps

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[Bigmonki]

Hi @afv2688 ,

Thanks for looking at this. So it looks like it might work... But!

I will receive a data file containing:

PRICECOD	RLASTW
PRICENUM(1)	1
PRICENUM(2)	97
PRICENUM(3)	98
PRICENUM(4)	99
PRICENUM(5)	3
PRICENUM(6)	2
PRICENUM(7)	<Null>
PRICENUM(8)	<Null>

The data may come like this:

PRICECOD	RWTLAS
PRICENUM(1)	1
PRICENUM(2)	2
PRICENUM(3)	3
PRICENUM(4)	97
PRICENUM(5)	98
PRICENUM(6)	99
PRICENUM(7)	<Null>
PRICENUM(8)	<Null>

What is consistent is that:

R = 1

W= 2

T = 3

L = 97

A = 98

S = 99

PRICENUM(1) = 1st letter in the PRICECOD string

PRICENUM(2) = 2nd letter in the PRICECOD string and so on.

I need to split the RLASTW string, match the position of the letter to the value in the PRICENUM(*) field and then make sure the combination is correct, so where ever R appears in the string, the relevant PRICENUM field contains a 1.

The attachment was an array we built in the hope that we might simplify things and use a look up. So effectively we break PRICECOD down into

R W T L A S

Go to our array table and find that combination:

Then make sure the sequence in our  data for PRICENUM fields matches the array sequence.

There may be a much simpler way to do this, but this is what we came up with before we found salvation in Alteryx!

[afv2688 · Accepted solution]

Hello @Bigmonki,

Keeps being difficult to understand sorry.

Here I have the price list on one side and the arrays on the other. I do compare them by order.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards