topic left join in a loop in QlikView

left join in a loop

adiarnon — Sun, 11 May 2014 14:58:18 GMT

hi,

i have a problem -

i have 4 DB with the same tables.

im creating a model that includes data from all of the DB.

i have a loop that run on each DB (different QVDS path)

when im doing left join its creating me 4 different tables (one for each loop) and not concatenate them.

someone know why? and how can i resolve it?

tnx,

adi.

Re: left join in a loop

SunilChauhan — Sun, 11 May 2014 15:01:44 GMT

could you please elaborate more about scnario.

Re: Re: left join in a loop

adiarnon — Sun, 11 May 2014 15:29:56 GMT

i added a print screen with the problem...

hope its more clear...

Re: left join in a loop

Joseph_Musekura — Sun, 11 May 2014 15:34:23 GMT

Hi,

You can force a concatenation you need to rename related field and use the same name.

T1:
LOAD

f1,

f2,

FROM file.QVD (qvd);

T2:
LOAD f4 as f1,

f5 as f2,

f6 as f3
FROM file1.QVD(qvd);

(se attached sample)

Re: left join in a loop

MarcoWedel — Sun, 11 May 2014 15:35:03 GMT

Here's a nice example for left joins in a loop:

Qlikview Notes: Use cases for Generic Load

Regards

Marco

Re: left join in a loop

fkeuroglian — Sun, 11 May 2014 16:23:56 GMT

Hi, first read this

good luck

Fernando

Understanding Join, Keep and Concatenate

Re: Re: left join in a loop

its_anandrjs — Sun, 11 May 2014 17:00:41 GMT

Load your table with same field names and if there is unique key fields then make join between then (Outer,Inner,Left,Right) which depends on the conditions of the table.

First Way

Table1:

Load * From Location;

Concatenate(Table1)

Table2:

Load * From Location;

Second Way

Table1:

Load

Field1,Field2,Field3

From Location;

Concatenate(Table1)

Table2:

Load

F1 as Field1,

F2 as Field2,

F3 as Field3

From Location;

Re: left join in a loop

Anonymous — Sun, 11 May 2014 18:09:48 GMT

If the tables had the same exact set of fields, the result will be auto-concatenated in one table. Apparently the fields are not all the same in your case. You can force concatenate all tables in one.

Start from creating an empty table, just a list of fields, no data. I's OK to start even with one field, for example:

DOCINV:

LOAD * INLINE [DOCINV_];

Next, in your loop, use CONCATENATE

CONCATENATE (DOCINV) LOAD

...

Now it doesn't matter if the field are the same or not. It will be one table. After that, review what you get and modify the script if necessary. Maybe you'll have to rename some fields, as Joseph Musekura suggested

Re: left join in a loop

rwunderlich — Mon, 12 May 2014 03:11:14 GMT

When tables share any field names other than a key, you cannot join them in a loop -- or in multiple operations. For example:

LEFT JOIN (Target) LOAD Key, FieldA FROM Source1;

LEFT JOIN (Target) LOAD Key, FieldA FROM Source2;

Nothing will be joined from Source2, because FieldA will be set to nulls in the first JOIN. This does not match the problem shown in your uploaded image, but it's a problem you're bound to run into. The solution is to collect all data in a temp table first and then do a single join.

temp:

LOAD Key, FieldA FROM Source1;

LOAD Key, FieldA FROM Source2;

LEFT JOIN (Target) RESIDENT temp;

DROP TABLE temp;

-Rob

Re: left join in a loop

adiarnon — Mon, 12 May 2014 05:31:13 GMT

this is my table code:

its in a loop that the $(V_COMP) change each loop

PORD:

LOAD
QCOMP&ORD AS COMP_PORD,
QCOMP&ORDI AS PO_COMP_PORDI,
QCOMP&PART AS PO_COMP_PART,
TBALANCE/1000 AS PO_TBALANCE,
PRICE AS PO_PRICE,
QPRICE AS PO_ITEMS_QPRICE,
TQUANT/1000 as PO_TQUANT,
QUANT/1000 as PO_QUANT,
UDATE AS PO_UDATE,
REQDATE AS PO_REQDATE,
CLOSED AS PORDI_CLOSED,
DUEDATE AS PO_DUEDATE,
DUE_DATE AS PO_DUE_DATE,
YEAR(DUE_DATE)AS YEAR_PO_DUE_DATE,
IF($(V_DT(ARRDATE))<>'01/01/1988',$(V_DT(ARRDATE))) AS 'תאריך יציאה מספק'

From
$(V_COMP)_PORDERITEMS.QVD
(QVD) ;

left join // COMP_PORD
LOAD

QCOMP&ORD AS COMP_PORD,

QCOMP&ORD AS COMP_PORDI,

QCOMP&SUP AS COMP_PORD_SUP
FROM
$(V_COMP)_PORDERS.QVD
(qvd);

Re: left join in a loop

lironbaram — Mon, 12 May 2014 06:42:18 GMT

hi because you are doing a left join inside the loop

when you load the PORD table from the second source it field list is different from the existing table

because it's missing the fields from the PORDERS

in the loop create two separate tables

and the do the left join out side the loop when each table has the records from all data sources