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

Omit dimension when not found

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Omit dimension when not found

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

6 Replies
Employee
Employee

Re: Omit dimension when not found

Table1:

Load FirstName, Color From Table1;

Concatenate (Table1)

Load Color, City From Table2;

HIC

MVP
MVP

Re: Omit dimension when not found

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.

Not applicable

Re: Omit dimension when not found

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

NEXT

Not applicable

Re: Omit dimension when not found

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

Employee
Employee

Re: Omit dimension when not found

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

Not applicable

Re: Omit dimension when not found

Perfect! Thank you very much, Henric.

Community Browser