Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
hani_saghir
Contributor III
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
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor III
Contributor III
Author

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

rwunderlich

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

-Rob

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

hani_saghir
Contributor III
Contributor III
Author

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