Mapping Matrix

[Newhaven]

Hy!

I need a mapping for a DataString

Example:

AX-1234

I have a Matrix like 

Zone1	Zone2	Zone3	Zone4
AB	CB	DS	AS
AC	AF	DR	AU
AD	AX		CV
			DX

So the mapping for the example should be: Zone2

-------------------------

An 2nd question:

After I have my Zone I have another Matrix with Prices:

length	Zone1	Zone2	Zone3	Zone4
0,5	1	2	3	4
1	5	6	7	8
1,5	9	10	11	12
2	13	14	15	16

So AX-1234; length 1,5

So Zone = Zone2

length = 1,5

Result = 10

Could you help me?

Thx

[Saravanan_Desingh]

I can't understand. How are you mapping AX-1234 to length 1,5?

I assumed, AX is the third element of Zone2. So, in the second table we can pull the length and Zone2 values of the third row. 

But I can't understand AX-1234. Where this -1234 comes?

[Newhaven]

AX-1234 is a postalcode of an delivery.
Delivery length is 1,5m

Pricelist is in two steps. Each zone has its own pricelist. Zone is the first two letters in the postalcode.
So first step i have to get the corect zone of the Zone Matrix.

Then i could check the price in the price Matrix…

[Saravanan_Desingh · Accepted solution]

I am not sure if I understand your requirement. But try the below code and let me know.

Zone:
LOAD RowNo() As RowID1, * INLINE [
    Zone1, Zone2, Zone3, Zone4
    AB, CB, DS, AS
    AC, AF, DR, AU
    AD, AX,  , CV
     ,  ,  , DX
];

Price:
LOAD RowNo() As RowID2, * INLINE [
    length, Zone1, Zone2, Zone3, Zone4
    "0.5", 1, 2, 3, 4
    1, 5, 6, 7, 8
    "1.5", 9, 10, 11, 12
    2, 13, 14, 15, 16
];

Input:
LOAD *, SubField(PostalCode,'-',1) As Zone INLINE [
    PostalCode
    AX-1234
];

ZX:
CrossTable(ZKeys,Zone)
LOAD RowID1 As RowID, Zone1, Zone2, Zone3, Zone4
Resident Zone;    

PX:
CrossTable(ZKeys,Price,2)
LOAD RowID2 As RowID, length, Zone1, Zone2, Zone3, Zone4
Resident Price;

Left Join(Input)
LOAD * Resident ZX;

Left Join(Input)
LOAD * Resident PX;

Drop Table Zone, Price, ZX, PX;

[Newhaven]

Works perfext. Thx!