Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

hani_saghir
New Contributor III

Concatenating fields as a single Data Field

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

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?

elie.issanick2009ali_hijazirwunderlichmaleksafa

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Concatenating fields as a single Data Field

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

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Concatenating fields as a single Data Field

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

hani_saghir
New Contributor III

Re: Concatenating fields as a single Data Field

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

MVP & Luminary
MVP & Luminary

Re: Concatenating fields as a single Data Field

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

-Rob

MVP & Luminary
MVP & Luminary

Re: Concatenating fields as a single Data Field

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

hani_saghir
New Contributor III

Re: Concatenating fields as a single Data Field

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