topic Re: How to extract a value from a data/string field in QlikView

How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 12:25:20 GMT

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

Re: How to extract a value from a data/string field

Chanty4u — Mon, 16 Apr 2018 12:29:27 GMT

try this

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

LOAD * Inline [

ID_1_TNO_93349345_RPK_1232_EPK_1111_CAR_ID_22

];

Re: How to extract a value from a data/string field

sasiparupudi1 — Mon, 16 Apr 2018 12:38:13 GMT

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

Re: How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 12:40:35 GMT

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....

Re: How to extract a value from a data/string field

Chanty4u — Mon, 16 Apr 2018 12:43:06 GMT

LOAD*

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

then load all

Re: How to extract a value from a data/string field

sasiparupudi1 — Mon, 16 Apr 2018 12:43:34 GMT

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);

Re: How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 12:45:54 GMT

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

Re: How to extract a value from a data/string field

bramkn — Mon, 16 Apr 2018 13:04:40 GMT

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.

Re: How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 13:08:41 GMT

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

Re: How to extract a value from a data/string field

sasiparupudi1 — Mon, 16 Apr 2018 13:14:24 GMT

Please provide some sample field value

Re: How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 13:28:21 GMT

Fuel_Consumption_View:
Load

COMBINED_KEY,
FUEL_COMBINED_KEY,

//TextBetween(FUEL_COMBINED_KEY,'TMO_','_RPK') AS FUEL_COMBINED_KEY_NUMERIC,

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

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);

This worked..Thanks for the support

Re: How to extract a value from a data/string field

trishita — Mon, 16 Apr 2018 13:28:42 GMT

Thanks for the support