topic Re: Concatenating fields as a single Data Field in QlikView

Concatenating fields as a single Data Field

hani_saghir — Thu, 09 Mar 2017 10:54:56 GMT

h‌i,

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,'','&');

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?

elie.issa‌nick2009‌ ali_hijazi‌rwunderlich‌maleksafa‌

Re: Concatenating fields as a single Data Field

marcus_sommer — Thu, 09 Mar 2017 13:53:02 GMT

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

Re: Concatenating fields as a single Data Field

hani_saghir — Thu, 09 Mar 2017 14:03:57 GMT

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

Re: Concatenating fields as a single Data Field

rwunderlich — Thu, 09 Mar 2017 15:18:16 GMT

Take a look in the Document Log to see what is being substituted for the variables.

-Rob

Re: Concatenating fields as a single Data Field

marcus_sommer — Thu, 09 Mar 2017 16:32:07 GMT

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

Re: Concatenating fields as a single Data Field

hani_saghir — Mon, 13 Mar 2017 20:01:49 GMT

worked like a charm. just had to adapt it to my case in which the log table is an oracle table.