topic Re: Cross Table with Embedded Labels AND explicit in QlikView

Cross Table with Embedded Labels AND explicit

shane_spencer — Mon, 24 Feb 2014 15:18:30 GMT

Is it possible to use a combination of Embedded Labels AND explicit for use on a cross table?

For example I've got 3 types of data file that I want to load in to Qlikview.

Column 1 and Column 2 are Qualifier Fields.

Column 1 header is always [00:00:01] and will be loaded as "Time",

Column 2 header changes from file to file and will be loaded as "Main_metric"

...so ideally I want to use Explicit values.

Column 3 and beyond are Attribute Fields and Data Fields.

The header in the raw data is needed for the Attribute Field so Imbedded labels are needed.

Ideally I want to replace 3 load scripts with 1, is that possible?

i.e. replace:

CrossTable(sub_Metric, value, 2)

LOAD [00:00:01] as Time,

IFACE as Main_metric,

[rxerr/s],

[txerr/s],

[coll/s],

[rxdrop/s],

[txdrop/s],

[txcarr/s],

[rxfram/s],

[rxfifo/s],

[txfifo/s]

CrossTable(sub_Metric, value, 2)

LOAD [00:00:01] as Time,

CPU as Main_metric,

%usr,

%nice,

%sys,

%iowait,

%steal,

%irq,

%soft,

%guest,

%idle

CrossTable(sub_Metric, value, 2)

LOAD [00:00:01] as Time,

DEV as Main_metric,

tps,

[rd_sec/s],

[wr_sec/s],

[avgrq-sz],

[avgqu-sz],

await,

svctm,

%util

...with a single load statement something like :

CrossTable(sub_Metric, value, 2)

LOAD [00:00:01] as Time,

@2 as Main_metric,

Re: Cross Table with Embedded Labels AND explicit

marcus_sommer — Mon, 24 Feb 2014 16:20:29 GMT

A mix of embedded label and no label isn't possible with txt-files. But you could easily load this few fields as column-no - see attachment.

- Marcus

Re: Cross Table with Embedded Labels AND explicit

shane_spencer — Tue, 25 Feb 2014 09:02:33 GMT

Hi Marcus, Thanx, but I have 5 files (with 3 different Headers in Column 2, and all 5 have different headers in the subsequent columns) so I would be more efficient to use embedded labels and have 3 load scripts rather than have 5 load scripts without embedded labels.

I suspect what I want to do is not possible, but I wondered if perhaps it could be done by maybe creating an empty table then loading data in to it.

Re: Cross Table with Embedded Labels AND explicit

marcus_sommer — Tue, 25 Feb 2014 09:16:34 GMT

I wouldn't say that is impossible but definitely not useful - different is different and every difference needed to be handled with some condition (perhaps in loops with variables and/or the rename-statement). But in your case with only a few tables/fields it would be faster and better to keep the script simple.

- Marcus

Re: Cross Table with Embedded Labels AND explicit

shane_spencer — Tue, 25 Feb 2014 09:59:30 GMT

Cheers Marcus - I was hoping because the files were so similar that there'd be an easy / graceful work-around. At present I can just do it with 3 load scripts but I've no idea if in the future there'll be more similar files to process that will require more scripts.