Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You may share Few Expressions then we may understand the better approach
Hi Anjani,
for your scenario In Notepad ++ we have an option
Replace all the location where ever . is there with \n
FYR...
PFA..
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.
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!