topic Setting field to define source in QlikView

Setting field to define source

Tue, 06 Oct 2015 18:57:33 GMT

i have three spreadsheets.

I want to load the spreadsheets and set an additional field so I know the source.

Below is my logic for loading the files

Crossreference:
LOAD ASSETNAME as 'hostname',
FROM

(ooxml, embedded labels, table is Sheet1);

noconcatenate;

snapshot:

LOAD ASSETNAME as 'hostname',
FROM
]
(ooxml, embedded labels, table is Sheet2);

noconcatenate;

assetinv:

LOAD ASSETNAME as 'hostname',
FROM

(ooxml, embedded labels, table is Sheet3);

If I join all three tables together, I get a master list of hostnames, but I lose the source of which file it resides in.

How can I create 'something' so I can create a table object and be able to see which file had the data.

Re: Setting field to define source

sunny_talwar — Tue, 06 Oct 2015 20:01:36 GMT

May be try this:

Crossreference:
LOAD ASSETNAME as 'hostname',

'Crossreference' as Source
FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(Crossreference)

LOAD ASSETNAME as 'hostname',

'Snapshot' as Source
FROM
]
(ooxml, embedded labels, table is Sheet2);

Concatenate(Crossreference)

LOAD ASSETNAME as 'hostname',

'Assetinv' as Source
FROM

(ooxml, embedded labels, table is Sheet3);

Re: Setting field to define source

maxgro — Tue, 06 Oct 2015 20:03:32 GMT

add a field for source

Crossreference:
LOAD

ASSETNAME as 'hostname',

1 as 1stSource

FROM

(ooxml, embedded labels, table is Sheet1);

1 as 2ndSource

1 as 3rdSource

Re: Setting field to define source

nagaiank — Tue, 06 Oct 2015 20:04:10 GMT

Try the following:

Crossreference:
LOAD ASSETNAME as 'hostname',
FROM
(ooxml, embedded labels, table is Sheet1);

LOAD ASSETNAME as 'hostname',
FROM ]
(ooxml, embedded labels, table is Sheet2);

LOAD ASSETNAME as 'hostname',
FROM
(ooxml, embedded labels, table is Sheet3);

Re: Setting field to define source

Anonymous — Wed, 07 Oct 2015 14:15:29 GMT

Thanks for the suggestions on the FirstSource, SecondSource, ThirdSource

My challenge now .. if I display the joined data in a Table Box, how can I search/filter where the hostname is only in FirstSource or the hostname is in the ThirdSource.

I think the blanks or nulls as a result of a join are stopping me from being able to filter on it.