joining two tables by shared field issue

[roisolberg]

Hi All

I am generating a big daily report that is combined from a few tables,

at the moment this report is cumulative and i want to turn this report to daily feeds (only the incremental addition from the previous day)

it came to a point that i have one very big table that has more then 100k rows(SerialOutDailyReport)

and another table that after some filters consists only the orders i want to show in the report -much smaller table(Delivery Update)

this is the tables as they appear in the script:

SerialOutDailyReport:

LOAD

    BoxNumber_Old,

    BoxName_Old AS Box_ID,

    ItemCode_Old,

    ReportDate_US AS CSV_Upload_Date,

    ReferenceNumber_Old,

    LastOCNumber_Old

FROM [lib://_administrator)/FactBox.qvd]

(qvd)

where VendorCode_Old='010012' AND IsStaticOrTransit='InTransitToCustomer';

left join

LOAD

    LastOCNumber_Old,

    DeliveryNumber_SAP,

    PickUpDate,

    SOid as Micro_SO

FROM [lib://_administrator)/DimDelivery.qvd]

(qvd);

LEFT JOIN

LOAD

    InnerNumber as BoxNumber_Old ,

    SerialNumber as Serial

FROM [lib://_administrator)/BoxSerials.qvd]

(qvd);

Store * From SerialOutDailyReport into [lib://_administrator)/SerialOutDailyReport_Temp1.qvd];

Drop table SerialOutDailyReport;

SerialOutDailyReport:

LOAD

    Serial,

    Box_ID,

    Micro_SO,

    PickUpDate,

    DeliveryNumber_SAP

FROM [lib://_administrator)/SerialOutDailyReport_Temp1.qvd]

(qvd)

WHERE LEN(Serial)>0;

Store SerialOutDailyReport into "lib://CSV/SHP$(vMicroSLP)_$(vDateS)_$(vTimeS)_F.csv"(txt, delimiter is '|');

Drop table SerialOutDailyReport;

and my other table is:

DeliveryUpdate:

LOAD

   LastOCNumber_Old,

    DeliveryNumber_SAP,

    PickUpDate,

    SOid,

    PickPackDate

FROM [lib://=_administrator)/DimDelivery.qvd] (qvd) where (DeliveryType='Sloc To Sloc' and ShipmentDate=(today()-1)) or PickPackDate=date(today()-1);

Store * From DeliveryUpdate into [lib://_administrator)/DeliveryUpdate.qvd];

drop table DeliveryUpdate;

the connection between the two tables is "LastOCNumber_Old".

i want the report to be the first table (SerialOutDailyReport) but only where the LastOCNumber_Old equals to the LastOCNumber_Old in the DeliveryUpdate table.

i have tried using inner join/right join/where clause but with no success, (each order number can have few serials)

If something is not clear enough i can attach some pics.

many many many thanks!

Roi

[YoussefBelloum]

Hi,

you can load SerialOutDailyReport table first and make a right join on it using DeliveryUpdate table, or you can load DeliveryUpdate  table first and make left join on it using SerialOutDailyReport  table.

[jyothish8807 · Accepted solution]

Hi Roi,

I believe inner join should work in this case: Try like this:

A:

Load *

from

["lib://CSV/SHP$(vMicroSLP)_$(vDateS)_$(vTimeS)_F.csv"(txt, delimiter is '|')]      // fix the path as per the actual name

inner join

load

distinct

LastOCNumber_Old

from [lib://_administrator)/DeliveryUpdate.qvd];

Br,

KC

[roisolberg]

Thank you very much guys!