Use of a reference number to separate 2 distinct categories

[mvtriff]

Hi everyone,

I need your help to separate 2 distinct categories (old units from new units) having a reference serial number in a consecutive series (please find attached a simple example). What formula should I use to get separate the 2 categories?

What I want to count is the Old units and the New Units, having the serial number as separator between the 2 categories.

If(Serial number<V12, 'old units')

If(Serial number>=V12, , 'new units')

But the things become complicated because the serial number format contains a letter followed by figures.

Many thanks.

[Dalton_Ruer · Accepted solution]

One way to accomplish it would be simply creating a Unit Type table that could be associated with the data:

Versions:
Load * Inline [
Version, UnitType
V1, Old
V6, Old
V7, Old
V12, Old
V13, New
V14, New
V17, New
V19, New
V22, New
V33, New
];

// Load the data
StockData:
Load * Inline [
Price, Text, MaterialNumber, Version
10000, t-shirt, 93849, V6
20000, pants, 93002, V13
10000, t-shirt, 88493, V22
30200, pants, 98390, V17
20000, pants, 18837, V7
23000, shirts, 93982, V12
22000, skirts, 39820, V19
22010, skirts, 3921983, V33
];

Second way would be to create a MAP and then do an APPLY MAP to handle the cross reference:

VersionsMap:
Mapping Load * Inline [
Version, UnitType
V1, Old
V6, Old
V7, Old
V12, Old
V13, New
V14, New
V17, New
V19, New
V22, New
V33, New
];

// Load the data
StockData:
Load Price, Text, MaterialNumber, Version, ApplyMap('VersionsMap', Version) as UnitType;
Load * Inline [
Price, Text, MaterialNumber, Version
10000, t-shirt, 93849, V6
20000, pants, 93002, V13
10000, t-shirt, 88493, V22
30200, pants, 98390, V17
20000, pants, 18837, V7
23000, shirts, 93982, V12
22000, skirts, 39820, V19
22010, skirts, 3921983, V33
];

Or you can simply use the REPLACE function to remove the V and just keep the numerics:

// Load the data
StockData:
Load Price, Text, MaterialNumber, Version, If(Replace(Version, 'V','') > 11, 'New', 'Old') as UnitType;
Load * Inline [
Price, Text, MaterialNumber, Version
10000, t-shirt, 93849, V6
20000, pants, 93002, V13
10000, t-shirt, 88493, V22
30200, pants, 98390, V17
20000, pants, 18837, V7
23000, shirts, 93982, V12
22000, skirts, 39820, V19
22010, skirts, 3921983, V33
];