How to extract a value from a data/string field

[trishita]

I have a specific field named Carl_Combined_Key which has values like :

ID_1_TNO_93349345_RPK_1232_EPK_1111_CAR_ID_22.

How can I only extract and display  93349345 only from the whole value

What function should I use, subsfield or trim..Can anyone please explain

[Chanty4u]

try this

A:

LOAD *,SubField(a,'_',4) as new;

LOAD * Inline [

a

ID_1_TNO_93349345_RPK_1232_EPK_1111_CAR_ID_22

];

[sasiparupudi1]

TextBetween('ID_1_TNO_93349345_RPK_1232_EPK_1111_CAR_ID_22','TNO_','_RPK')

[trishita]

The script is:

LOAD

COMBINED_KEY,

FUEL_COMBINED_KEY,

FC_CONSUMPTIVE_ENGINE_ID,

FC_MASS,

FC_FUEL_CONSUMPTION_IN_TONS,

FC_VALID_FROM_UTC,

FC_VALID_TO_UTC,

FC_VALID_FROM_TIMEZONE,

FC_VALID_TO_TIMEZONE

FROM

[$(vG.QVDPath)MO_NI_FUEL_CONSUMPTION_VIEW.qvd]

(qvd);

here  ID_1_TNO_93349345_RPK_1232_EPK_1111_CAR_ID_22 corresponds to COMBINED_KEY..so how can I modify the script....

[Chanty4u · Accepted solution]

LOAD*

subfield(COMBINED_KEY,'_',4)  as NewCombinedKey;

then load all

[sasiparupudi1]

LOAD

COMBINED_KEY,

TextBetween(COMBINED_KEY,'TNO_','_RPK') AS COMBINED_KEY_NUMERIC,

FUEL_COMBINED_KEY,

FC_CONSUMPTIVE_ENGINE_ID,

FC_MASS,

FC_FUEL_CONSUMPTION_IN_TONS,

FC_VALID_FROM_UTC,

FC_VALID_TO_UTC,

FC_VALID_FROM_TIMEZONE,

FC_VALID_TO_TIMEZONE

FROM

[$(vG.QVDPath)MO_NI_FUEL_CONSUMPTION_VIEW.qvd]

(qvd);

[trishita]

what operation should I impose on the data field Combined_Key but not on individual data as the table has tonnes of data

[bramkn]

It all depends on the data.

If the number is always between the same 2 words you can use those words with the textbetween function.

If it is always in the same spot with the same amount of '_' delimiters before it. Then use the subfield function.

If none of these apply you will need to figure out how it is structured and how you can find it. for example if it is always the first number with a length more than 3 you can use a comination of subfield,len and isnum to find it.

[trishita]

Fuel_Consumption_View:
Load

COMBINED_KEY,
FUEL_COMBINED_KEY,

TextBetween(FUEL_COMBINED_KEY,'TMO_','_RPK') AS IMO_NO_FUEL_CONSUMPTION,

FC_CONSUMPTIVE_ENGINE_ID,
FC_MASS,
FC_FUEL_CONSUMPTION_IN_TONS,
FC_VALID_FROM_UTC,
FC_VALID_TO_UTC,
FC_VALID_FROM_TIMEZONE,
FC_VALID_TO_TIMEZONE

FROM

[$(vG.QVDPath)MO_NI_FUEL_CONSUMPTION_VIEW.qvd]

(qvd);

ITS POPULATING ALL DATA EXCEPT THE IMO_NO_FUEL_CONSUMPTION ::ITS NOT WORKING

[sasiparupudi1]

Please provide some sample field value