Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qlikconsultant
Contributor II

Load different files and join. Difference betwean concatenate and not?

Hi i have the following code:

Table_1:

LOAD

Attribute_A,

Attribute_B

FROM X.qvd (qvd);

left join Table_2:

Load

Attribute_B,

Attribute_C

FROM Y1.qvd (qvd);

Load

Attribute_B,

Attribute_C

FROM Y2.qvd (qvd);

Load

Attribute_B,

Attribute_C

FROM Y3.qvd (qvd);

If I use this code i have a Table_2 in the Data_Structure. If I use Concatenate:

Table_1:

LOAD

Attribute_A,

Attribute_B

FROM X.qvd (qvd);

left join Table_2:

Load

Attribute_B,

Attribute_C

FROM Y1.qvd (qvd);

Concatenate

Load

Attribute_B,

Attribute_C

FROM Y2.qvd (qvd);

Concatenate

Load

Attribute_B,

Attribute_C

FROM Y3.qvd (qvd);

I don't have Table_2 is not in the Data_Structure.

I thought both codes should deliver the same result. Is it because of the join that QlikView uses the joins after the first tables and ignore the other 2 tables?

1 Solution

Accepted Solutions

Re: Load different files and join. Difference betwean concatenate and not?

// The first case:


Table_1: 
LOAD  Attribute_A,  Attribute_B  FROM X.qvd (qvd); 
 

// table Table_1 is created


left join 


// the data from next load statement will be joined to Table_1


// Table_2 will not be created since it will be joined into Table_1

Table_2:                                   
Load   Attribute_B,   Attribute_C  FROM Y1.qvd (qvd); 

 

// Only Table_1 exists and it contains the data from X.qvd and Y1.qvd

// The next load statement will create a new table. Since you don't specify a table name it will get the name from the table stored in the qvd

Load   Attribute_B,  Attribute_C  FROM Y2.qvd (qvd); 

// There are now two tables.


// This load retrieves the same fields as the load from Y2.qvd. Therefore the data will be appened to that table
Load   Attribute_B,   Attribute_C  FROM Y3.qvd (qvd); 

// There are still two tables: Table_1 with data from X.qvd and Y1.qvd and another table with data from Y2.qvd and Y3.qvd.

// Second case:

//Because you now concatenate the data from Y2.qvd and Y3.qvd all the data ends up in Table_1.

// Third case:

// First load all the data from Y1.qvd, Y2.qvd and Y3.qvd into one table

Table_1:

LOAD  Attribute_B,   Attribute_C  FROM Y*.qvd (qvd); 


JOIN (Table_1)


// Then join to that table the data from X.qvd

LOAD Attribute_A,  Attribute_B  FROM X.qvd (qvd);

// This will probably give you the result you want.


talk is cheap, supply exceeds demand
9 Replies
sureshqv
Esteemed Contributor III

Re: Load  different files and join. Difference betwean concatenate and not?

Re: Load different files and join. Difference betwean concatenate and not?

I am not sure what you are trying to do but correct way here is

Table_2: 

Load  

Attribute_B,  

Attribute_C 

FROM Y1.qvd (qvd); 

Concatenate 

Load  

Attribute_B, 

Attribute_C 

FROM Y2.qvd (qvd); 

Concatenate  

Load  

Attribute_B,  

Attribute_C 

FROM Y3.qvd (qvd); 

right join (Table_2)

LOAD 

Attribute_A, 

Attribute_B 

FROM X.qvd (qvd); 

Re: Load different files and join. Difference betwean concatenate and not?

May be you want to do this:

Table_1: 

LOAD 

Attribute_A, 

Attribute_B 

FROM X.qvd (qvd); 

 

Table_2: 

Load  

Attribute_B,  

Attribute_C 

FROM Y1.qvd (qvd);

 

Concatenate (Table_2)

Load  

Attribute_B, 

Attribute_C 

FROM Y2.qvd (qvd);

Concatenate (Table_2)  

Load  

Attribute_B,  

Attribute_C 

FROM Y3.qvd (qvd);

Left Join (Table_1)

LOAD *

Resident Table_2;

DROP Table Table_2;

qlikconsultant
Contributor II

Re: Load different files and join. Difference betwean concatenate and not?

I try to join both tables but table_1 is filled with a loop.

neha_shirsath
Valued Contributor

Re: Load different files and join. Difference betwean concatenate and not?

Hi,

What exactly you are trying to do?

You want difference between Concatenate & join?

you can refer below discussion-

Re: What is difference between concatenate & join?

Regards,

Neha

Re: Load different files and join. Difference betwean concatenate and not?

So have you tried what I have suggested?

Re: Load different files and join. Difference betwean concatenate and not?

If your tables only have two columns, consider using ApplyMap rather than join as the results will be more consistent and faster.

These blogs should help

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Don't join - use Applymap instead

Highlighted

Re: Load different files and join. Difference betwean concatenate and not?

Try this


BC_Map:

Mapping Load

     Attribute_B,  

     Attribute_C 

FROM Y*.qvd (qvd); 


Table_1: 

LOAD 

     Attribute_A, 

     Attribute_B .

     ApplyMap('BC_Map', Attribute_B) as Attribute_C

FROM X.qvd (qvd); 

Re: Load different files and join. Difference betwean concatenate and not?

// The first case:


Table_1: 
LOAD  Attribute_A,  Attribute_B  FROM X.qvd (qvd); 
 

// table Table_1 is created


left join 


// the data from next load statement will be joined to Table_1


// Table_2 will not be created since it will be joined into Table_1

Table_2:                                   
Load   Attribute_B,   Attribute_C  FROM Y1.qvd (qvd); 

 

// Only Table_1 exists and it contains the data from X.qvd and Y1.qvd

// The next load statement will create a new table. Since you don't specify a table name it will get the name from the table stored in the qvd

Load   Attribute_B,  Attribute_C  FROM Y2.qvd (qvd); 

// There are now two tables.


// This load retrieves the same fields as the load from Y2.qvd. Therefore the data will be appened to that table
Load   Attribute_B,   Attribute_C  FROM Y3.qvd (qvd); 

// There are still two tables: Table_1 with data from X.qvd and Y1.qvd and another table with data from Y2.qvd and Y3.qvd.

// Second case:

//Because you now concatenate the data from Y2.qvd and Y3.qvd all the data ends up in Table_1.

// Third case:

// First load all the data from Y1.qvd, Y2.qvd and Y3.qvd into one table

Table_1:

LOAD  Attribute_B,   Attribute_C  FROM Y*.qvd (qvd); 


JOIN (Table_1)


// Then join to that table the data from X.qvd

LOAD Attribute_A,  Attribute_B  FROM X.qvd (qvd);

// This will probably give you the result you want.


talk is cheap, supply exceeds demand
Community Browser