join between two qvd

[geogou1973]

Hello.

I'm facing a problem with join two qvd files.

The one qvd file has 37000 records and the other has 14000 records.

I try to have all the records from the file of 37000 and in the same ITEM of the second file to take the changes.

i have the following script but i take only the records that matching in the field ITEM.

Can anyone help me in this.

LOAD

     STKDAT,

     FBPCS AS ITEM,

     FGIMA,

     QTY

FROM

STOCK_131.QVD (qvd);

LEFT JOIN

LOAD

      INQTY,

      OUTQTY,

      ITEM,

      STORE_ID

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131;

Thank you in advance.

[jonathandienst · Accepted solution]

Or this:

Map_Move:

Mapping LOAD

      ITEM,

      Sum(INQTY) - Sum(OUTQTY) As Move

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131

GROUP BY ITEM

;

Data:

LOAD *,

  RangeSum(QTY, Move) As FinalQTY

LOAD

     STKDAT,

     FBPCS AS ITEM,

     FGIMA,

     QTY,

  ApplyMap('Map_Move', FBPCS, 0) As Move

FROM

STOCK_131.QVD (qvd);

[Anonymous]

Did not get you what exactly you want, but when it comes to join:

1)

Table1

left join

Table2

Result: All the records from Table1 and common records from Table2

2)

Table1

Right join

Table2

Result: All the records from Table2 and common records from Table1

1)

Table1

Full join

Table2

Result: All the records from Table1 & Table2

1)

Table1

inner join

Table2

Result: Common records from both the table

Hope this will clear you about Join!!

[preminqlik]

am not sure, but as per my understanding by your details, follow the below proce without joining

TABLE_1_37000:

LOAD

     STKDAT,

     FBPCS AS ITEM,

     FGIMA,

     QTY

FROM

STOCK_131.QVD (qvd);

TABLE_2_14000:

left keep (TABLE_1_37000)

LOAD

      INQTY,

      OUTQTY,

      ITEM,

      STORE_ID

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131;

[geogou1973]

Jonathan it is correct. Thank you very much.