topic Re: Omit dimension when not found in QlikView

Omit dimension when not found

Sun, 25 Nov 2012 13:24:03 GMT

Hi All,

using a loop I need to load data from tables where some dimensions are common between the tables, some are not. What should I put into load code so that all tables can be loaded by a single load (i.e. when dimension was not found load all other without raising an error)? In the below example I'd like to load all data from Table1 and Table2, using a single load code, to get Table3.

Regards,

Przemek

INPUT

Table1:

FirstName, Color

John, Blue

Table2:

Color, City

Red, Berlin

OUTPUT

Table3:

FirstName, Surname, City

John, Blue, Null

Null, Red, Berlin

Re: Omit dimension when not found

hic — Sun, 25 Nov 2012 13:33:45 GMT

Table1:

Load FirstName, Color From Table1;

Concatenate (Table1)

Load Color, City From Table2;

HIC

Re: Omit dimension when not found

swuehl — Sun, 25 Nov 2012 13:38:52 GMT

Not sure what you mean with 'loaded by a single load', I think you need two load statements when you adressing two different table sources.

Besides this, I think you can just load the two tables, QV will link the tables by Color field and should handle your request properly.

Table1:

LOAD * INLINE [

FirstName, Color

John, Blue

];

Table2:

LOAD * INLINE [

Color, City

Red, Berlin

];

Or just add a JOIN before the second load, to join both tables, resulting in one final table:

Table1:

LOAD * INLINE [

FirstName, Color

John, Blue

];

Table2:

JOIN LOAD * INLINE [

Color, City

Red, Berlin

];

edit: Just noticed Henrics suggestions, which is another alternative. I think it depends how you want to handle the records in both tables that may have same COLOR values.

Re: Omit dimension when not found

Sun, 25 Nov 2012 13:42:16 GMT

Not that easy thing - there are thousands of tables. I look for a kind of a metafunction (let's call it NotFound()) that would check if dimension exists and omit it if not e.g.:

FOR i=1 to 10000

LET Table = 'Table'&$(i);

LOAD

If(NotFound(FirstName), '', FirstName) AS FirstName,

If(NotFound(Color), '', Color) AS Color,

If(NotFound(City), '', City) AS City

FROM $(Table);

Re: Omit dimension when not found

Sun, 25 Nov 2012 13:44:35 GMT

Well, the thing is I need to do it in a single statment...Otherwise will spend days creating statements.

Re: Omit dimension when not found

hic — Sun, 25 Nov 2012 13:59:05 GMT

Or if you want ti do it in a loop, you can do something like:

Set vConcatenate = ;

For each vFile in FileList('C:\directory\*.csv')

$(vConcatenate)

Load * from [$(vFile)] ... ;

Set vConcatenate = Concatenate ;

Next vFile

HIC

Re: Omit dimension when not found

Sun, 25 Nov 2012 14:33:14 GMT

Perfect! Thank you very much, Henric.