Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
];