Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to extract capacity from this exemple :
Exemple :
from "5L Glaceol Rafinum -5°C" extract "5"
from "208L w40 Total DZ" extract "208"
from "0.2L Lubrex Degrippant" extract "0.2"
Data sample :
0.2L Lubrex Degrippant
0.5L Naftilia Moto 2T
0.7L Eau Déminéralisée ASA
0.9L ATF Pro Fors Dexron DII
0.9L ATF TopAsia
0.9L SAE90 Top Asia Gear oil
1.5L G12 Bendix RED Concentré
1.5L G12+ ATQ Concentré
1.5L G12+ ROWE Concentré
1.5L G13 ATQ Concentré
1KG Graisse Lotos
5L Glaceol Rafinum -5°C
5L Glaciol EGDP
5L Lave Glace HBCH
5L Lave Glace Naftal
5L Lave Glace Pomaria
5L S40 AVIN Force 4s3
5L SAE40 Lotos Ibis Rdeo
5w30 Mannol TS17 UHPD BLUE
10L Nettoyant moteur Etoile Glanze
10w30 Liquimoly Marine 4T
20L 15w40 Mannol E7 TS4 SHPD
20L SAE40 Lotos Superol CB
20L SAE50
20L SAE90 Lotos
60L 5w30 castrol Edge LL
208L 5w30 Castrol EDGE BMW LL-04
208L 5w30 Castrol EDGE C3
208L 5w30 Castrol EDGE LongLife III
208L 15w40 Chempioil Truck SHPD E7
208L 15w40 Rowe Hightec Turbo E7
208L w40 Chempioil
208L w40 Total DZ
AdBlue 5L Tradex
AdBlue ROWE
AdBlue Volkswagen
Ampoule 1 Plomb NARVA Orange PY21W 12V 21W
Ampoule 1 Plomb OSRAM P21W 12V ORIGINAL
Thanks
Something like this?
Products:
NOCONCATENATE
LOAD
RecNo() AS RowIDTemp,
*
INLINE [
ProductDescription
0.2L Lubrex Degrippant
0.5L Naftilia Moto 2T
0.7L Eau Déminéralisée ASA
0.9L ATF Pro Fors Dexron DII
0.9L ATF TopAsia
0.9L SAE90 Top Asia Gear oil
1.5L G12 Bendix RED Concentré
1.5L G12+ ATQ Concentré
1.5L G12+ ROWE Concentré
1.5L G13 ATQ Concentré
1KG Graisse Lotos
5L Glaceol Rafinum -5°C
5L Glaciol EGDP
5L Lave Glace HBCH
5L Lave Glace Naftal
5L Lave Glace Pomaria
5L S40 AVIN Force 4s3
5L SAE40 Lotos Ibis Rdeo
5w30 Mannol TS17 UHPD BLUE
10L Nettoyant moteur Etoile Glanze
10w30 Liquimoly Marine 4T
20L 15w40 Mannol E7 TS4 SHPD
20L SAE40 Lotos Superol CB
20L SAE50
20L SAE90 Lotos
60L 5w30 castrol Edge LL
208L 5w30 Castrol EDGE BMW LL-04
208L 5w30 Castrol EDGE C3
208L 5w30 Castrol EDGE LongLife III
208L 15w40 Chempioil Truck SHPD E7
208L 15w40 Rowe Hightec Turbo E7
208L w40 Chempioil
208L w40 Total DZ
AdBlue 5L Tradex
AdBlue ROWE
AdBlue Volkswagen
Ampoule 1 Plomb NARVA Orange PY21W 12V 21W
Ampoule 1 Plomb OSRAM P21W 12V ORIGINAL
];
Temp:
NOCONCATENATE
LOAD *,
If(Right(ProductCapacityTemp, 1) = 'L'
AND IsNum(Left(ProductCapacityTemp, Len(ProductCapacityTemp)-1)),
Num(Replace(ProductCapacityTemp, 'L', ''))
) AS ProductCapacity
;
LOAD *,
Trim(Replace(SubField(ProductDescription, ' '), '.', ',')) AS ProductCapacityTemp
RESIDENT
Products
;
LEFT JOIN (Products)
LOAD
RowIDTemp,
ProductCapacity
RESIDENT
Temp
WHERE
NOT IsNull(ProductCapacity)
AND RowIDTemp <> Peek(RowIDTemp)
ORDER BY
RowIDTemp,
ProductCapacity
;
DROP TABLE Temp;
DROP FIELD RowIDTemp FROM Products;
Can you describe how you would manually extract those values from given strings?
Based on what rules would you decide which numbers to use?
Thanks
The values I want to extract all start with numbers and are in the first sentence like "208L". "20L" or "5L" and the "L" defines the Liter,
I just need to have the capacities apart from doing mathematical operations
Something like this?
Products:
NOCONCATENATE
LOAD
RecNo() AS RowIDTemp,
*
INLINE [
ProductDescription
0.2L Lubrex Degrippant
0.5L Naftilia Moto 2T
0.7L Eau Déminéralisée ASA
0.9L ATF Pro Fors Dexron DII
0.9L ATF TopAsia
0.9L SAE90 Top Asia Gear oil
1.5L G12 Bendix RED Concentré
1.5L G12+ ATQ Concentré
1.5L G12+ ROWE Concentré
1.5L G13 ATQ Concentré
1KG Graisse Lotos
5L Glaceol Rafinum -5°C
5L Glaciol EGDP
5L Lave Glace HBCH
5L Lave Glace Naftal
5L Lave Glace Pomaria
5L S40 AVIN Force 4s3
5L SAE40 Lotos Ibis Rdeo
5w30 Mannol TS17 UHPD BLUE
10L Nettoyant moteur Etoile Glanze
10w30 Liquimoly Marine 4T
20L 15w40 Mannol E7 TS4 SHPD
20L SAE40 Lotos Superol CB
20L SAE50
20L SAE90 Lotos
60L 5w30 castrol Edge LL
208L 5w30 Castrol EDGE BMW LL-04
208L 5w30 Castrol EDGE C3
208L 5w30 Castrol EDGE LongLife III
208L 15w40 Chempioil Truck SHPD E7
208L 15w40 Rowe Hightec Turbo E7
208L w40 Chempioil
208L w40 Total DZ
AdBlue 5L Tradex
AdBlue ROWE
AdBlue Volkswagen
Ampoule 1 Plomb NARVA Orange PY21W 12V 21W
Ampoule 1 Plomb OSRAM P21W 12V ORIGINAL
];
Temp:
NOCONCATENATE
LOAD *,
If(Right(ProductCapacityTemp, 1) = 'L'
AND IsNum(Left(ProductCapacityTemp, Len(ProductCapacityTemp)-1)),
Num(Replace(ProductCapacityTemp, 'L', ''))
) AS ProductCapacity
;
LOAD *,
Trim(Replace(SubField(ProductDescription, ' '), '.', ',')) AS ProductCapacityTemp
RESIDENT
Products
;
LEFT JOIN (Products)
LOAD
RowIDTemp,
ProductCapacity
RESIDENT
Temp
WHERE
NOT IsNull(ProductCapacity)
AND RowIDTemp <> Peek(RowIDTemp)
ORDER BY
RowIDTemp,
ProductCapacity
;
DROP TABLE Temp;
DROP FIELD RowIDTemp FROM Products;
just a small clarification, I bring back the "Product Description" data like this:
SQL SELECT "id" as "Product_id",
"last_price_purchase",
"Mark",
"name" as "Products_name",
"price",
"price_purchase",
"product_type" as "Id_Product_type",
"ref",
"ref_aftm",
"ref_oem" as "OEM",
"stock",
"wholesale_price",
"oem_changed"
FROM "mechanical_workshop2". "public".."auto_product";
Postgresql database table "auto_product" column "name" as "Products_name",
in this case, as I modify your scrip, it works perfectly
sorry for the lack of details and thank you very much