Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Setting field to define source

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.

4 Replies

Re: Setting field to define source

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);

MVP
MVP

Re: Setting field to define source

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

nagaiank
Valued Contributor III

Re: Setting field to define source

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);

Highlighted
admin1
New Contributor III

Re: Setting field to define source

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.