Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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);

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.

Community Browser