left join in a loop

[adiarnon]

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.

[SunilChauhan]

could you please elaborate more about scnario.

[adiarnon]

i added a print screen with the problem...

hope its more clear...

[Joseph_Musekura]

Hi,

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

T1:
LOAD

f1,

f2,

f3

FROM file.QVD (qvd);

T2:
LOAD f4 as f1,

    f5 as f2,

    f6 as f3
FROM file1.QVD(qvd);

(se attached sample)

[MarcoWedel]

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

Qlikview Notes: Use cases for Generic Load

Regards

Marco

[fkeuroglian]

Hi, first read this

good luck

Fernando

Understanding Join, Keep and Concatenate

[its_anandrjs]

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;

[Anonymous]

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

<loop start>

  CONCATENATE (DOCINV) LOAD

  ...

<loop end>

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

[rwunderlich]

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

[adiarnon]

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