topic Re: How to Extract the text between Special Characters in QlikView

How to Extract the text between Special Characters

divianjani — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,
I need one help,
I have one Field with many expressions as a records. One record sample is mentioned below,
FILTER(EDW."Fact - Concession Line Item"."Ticket Quantity" - EDW."Fact - Concession Line Item"."Refund Ticket Quantity" USING EDW."Dim - Ticket Category"."Ticket Pay Code" = 'PAID').

From the above record, I want the above field names which are mentioned in double quotes (eg;"Fact - Concession Line Item" ) as a Text for the separate column name.Like this I have many expressions . How can I do the above requirement by coding in qlikview.

Thanks in Advance

Anjani

Re: How to Extract the text between Special Characters

Anil_Babu_Samineni — Wed, 18 Jan 2017 06:42:47 GMT

You may share Few Expressions then we may understand the better approach

Re: How to Extract the text between Special Characters

ramasaisaksoft — Wed, 18 Jan 2017 06:44:30 GMT

Hi Anjani,

for your scenario In Notepad ++ we have an option

Replace all the location where ever . is there with \n

FYR...

PFA..

Re: How to Extract the text between Special Characters

jonathandienst — Wed, 18 Jan 2017 07:04:04 GMT

You did not say in what form you would like the results. I am assuming that we will join them back to the main table.

One way is to tokenise the data, using " as a separator, and take every other token as the field names. Assuming a table called Data, and a field named Field, use code like:

// Tokenise the field:

T_Extract:

LOAD Field,

SubField(Field, '"') as tokens,

RowNo() as row

Resident Data;

// Get the base row number for each value of Field

Join(T_Extract)

LOAD Field,

Min(row) as minrow

Resident T_Extract

Group by Field;

// Concat tokens 1, 3, 5, .... (data inside the "s in the source) and join back to the main table

// (discard tokens 0, 2, 4, ... (data outside the "s)

Join(Data)

LOAD Field,

Concat(tokens, '|') as fieldlist

Resident T_Extract

Where Mod((row - minrow), 2) = 1

Group By Field;

DROP Table T_Extract;

Adapt to suit your table and field names. If you need the data in a different form, be more specific about your requirements and upload a sample qvw and a sample source file containing representative data.

Re: How to Extract the text between Special Characters

Anonymous — Wed, 18 Jan 2017 07:11:44 GMT

Hi,

You could get these values using preceeding loads.

Should you have other fields in that table use *, to load all other fields.

i.e:

Data:
// 3rd preceeding load
Load RecNum,
if(WildMatch(Fields,'*-*'),trim(subfield(Fields,'-',2)),Fields) as Fields;
// 2nd preceeding load
Load RecNum,
subfield(Fields,'"',2) as Fields;
// 1st preceeding load
load
RecNo() as RecNum,
SubField(Values,'.') as Fields;
// Your Data loaded inline into field called Values
LOAD * INLINE [
Values
EDW."Fact - Concession Line Item"."Ticket Quantity" - EDW."Fact - Concession Line Item"."Refund Ticket Quantity" USING EDW."Dim - Ticket Category"."Ticket Pay Code" = 'PAID'
];
Output:

Fields:

Concession Line Item

Refund Ticket Quantity

Ticket Category

Ticket Pay Code

Ticket Quantity

Hope this helps!

Re: How to Extract the text between Special Characters

beck_bakytbek — Wed, 18 Jan 2017 16:06:10 GMT

hi divi,

check this, it resolves your issue:

https://www.youtube.com/watch?v=m5h70rj629g

beck