Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i have a need to concat multiple fields of multiple qvd files into 1 data field, used for searching.
for example: QVD1 contains A, B, and C, and QVD2 contains D and E.
the needed output is:
A:aaa | B:bbb | C:ccc
D:ddd | E:eee
I'm successfully looping the QVDs and storing them in a temp table 'tmp'
the below script successfully concats the fields, but i'm not able to insert the fields name.
i am able to have:
aaabbbccc
dddeee
[...looping multiple QVDs...]
tmp:
Load *
From '$(vQvdFile)' (qvd);
let fields = '';
FOR j = NoOfFields('tbl') to NoOfFields('tmp') - 1
let fld = FieldName($(j), 'tmp');
let fields = '$(fields)' & '$(fld)' & if($(j)=NoOfFields('tmp') - 1,'','&');
Next
trace '[$(fields)]';
data:
load $(fields) as Data
Resident tmp;
drop Table tmp;
end if
any idea how i can insert the field name into the Data?
In this case I would rather try to load the data with "normal" table-loadings, for example per crosstable like in this example:
CrossTable:
crosstable(Category, Value, 5)
LOAD rowno() as RowNo, *
FROM [..\..\..\..\QlikViewServer\ProgramData\QlikViewServer\Performance_2017-03.log]
(txt, utf8, embedded labels, delimiter is '\t', no quotes);
Final:
load
[Exe Type], [Exe Version], [Server Started], Timestamp,
concat(Category & ' = ' & Value, chr(10), RowNo) as Detail
resident CrossTable group by [Exe Type], [Exe Version], [Server Started], Timestamp;
drop table CrossTable;
which will create a concatenated Detail-field which contained the category and the value in a ini-like style.
- Marcus
What is the aim behind your attempt? Qlik has a native feature to search values in different fields at the same time, for example within a searchbox or per advanced search in a listbox - I could imagine that this served your needs better than creating new tables/fields for them.
- Marcus
Your reply is appreciated.
my business case is that i have 30 error log tables, all tables have the same 1st 15 fields that contain general info, and they differ in detail logging fields.
i don't wish to end up with a log table with 100+ fields, but prefer to have a table box showing the general fields, and Data log field which concatenates all details.
hope it makes sense to you
Take a look in the Document Log to see what is being substituted for the variables.
-Rob
In this case I would rather try to load the data with "normal" table-loadings, for example per crosstable like in this example:
CrossTable:
crosstable(Category, Value, 5)
LOAD rowno() as RowNo, *
FROM [..\..\..\..\QlikViewServer\ProgramData\QlikViewServer\Performance_2017-03.log]
(txt, utf8, embedded labels, delimiter is '\t', no quotes);
Final:
load
[Exe Type], [Exe Version], [Server Started], Timestamp,
concat(Category & ' = ' & Value, chr(10), RowNo) as Detail
resident CrossTable group by [Exe Type], [Exe Version], [Server Started], Timestamp;
drop table CrossTable;
which will create a concatenated Detail-field which contained the category and the value in a ini-like style.
- Marcus
worked like a charm. just had to adapt it to my case in which the log table is an oracle table.