Concatenation and Lookups

[#1]

I have 3 tables which I have concatenated.

Table Name	ShipmentId	Amount
Table 1	1	£10
Table 1	2	£20
Table 1	3	£30

Table Name	ReceiptId	Amount
Table 2	1	£10
Table 2	2	£20
Table 2	3	£30

Table Name	Shipment id	Time
Table 3	1	07:00
Table 3	2	08:00
Table 3	3	09:00

The table currently looks like this:

Table Name	ShipmentId	ReceiptID	Amount	Time
Table 1	1	-	£10
Table 1	2	-	£20
Table 1	3	-	£30
Table 2	-	1	£10
Table 2	-	2	£20
Table 2	-	3	£30
Table 3	1	-	-	07:00
Table 3	2	-	-	08:00
Table 3	3	-	-	09:00

What I need to do is apply a "lookup" to Table 3 against table 1 using the "shipment ID" so that the table looks like this:

Table Name	ShipmentId	ReceiptID	Amount	Time
Table 1	1	-	£10
Table 1	2	-	£20
Table 1	3	-	£30
Table 2	-	1	£10
Table 2	-	2	£20
Table 2	-	3	£30
Table 3	1	-	£10	07:00
Table 3	2	-	£20	08:00
Table 3	3	-	£30	09:00

Can someone help me apply this lookup?

Thanks

[jagan]

Hi,

Try like this

Data:

LOAD

*

FROM Table3;

LEFT JOIN(Data)

LOAD

ShipmentID,

Amount

FROM Table1;

Concatenate(Data)

LOAD

*

FROM Table1;

Concatenate(Data)

LOAD

*

FROM Table2;

Hope this helps you.

Regards,

Jagan.

[ashfaq_haseeb]

You need to join table 1 and table 3.

Then concatenate this result with table 2.

Regards

ASHFAQ

[anbu1984]

Initial:

Load * Inline [

Table_Name,ShipmentId,Amount

Table 1,1,£10

Table 1,2,£20

Table 1,3,£30 ];

Concatenate

Load * Inline [

Table_Name,ReceiptId,Amount

Table 2,1,£10

Table 2,2,£20

Table 2,3,£30 ];

Concatenate

Load * Inline [

Table_Name,ShipmentId,Time

Table 3,1,07:00

Table 3,2,08:00

Table 3,3,09:00 ];

NoConcatenate

Final:

Load *, If( Not(IsNull(ShipmentId)) And ShipmentId = Previous(ShipmentId),Previous(Amount),Amount) Resident Initial Order by ShipmentId,Amount desc;

Drop table Initial;

[#5]

I think you are right but unfortunately my actual table has more tables and more columns - when I tried to rename the concatenates with Left joins it doesn't appear to have pulled through the left join tables....

In my example, the "Table Name" column is a column which I am creating in the script:

'Table 1' as 'Table Name',

.

.

.......;

SQL Select *

From Table.Table;

Concatenate(...)

Load

'Table 2' as 'Table Name'

etc etc

There are also columns which are named the same but are not related, this is why I have used the concatenate. The lookups I need to apply are relatively few - is there a way to write a lookup formula instead?

[#6]

lookup('AMOUNT','ShipmentId','ShipmentId,'TABLE1') AS AMOUNT

[anbu1984]

Did you check my script below without join

[#8]

My script writing isn't really good enough to relay what you've put into my "real" data

Will it help if I send my script at the moment? It's pretty long....

[anbu1984]

Yes

[#10]

CONNECT32 TO dbserver;
Snap:
Load
    'SHIPMENT' as Table,
    BizId,
    CarrierId,
    CarrierTrackingNumber,
    Charges,
    ConsigmentId,
    ConsignmentId,
    CustomerId,
    CustomerName,
    CustomerRef,
    DateClosed,
    DateCreated,
    Month(DateCreated) as Month,
    Week(DateCreated) as Week,
    Year(DateCreated) as Year,
    Date(DateCreated) as Date,
    DateDueOut,
    DateShipment,
    Freight,
    LineQty,
    `Lines`,
    LineValue,
    OwnerId as Client,
    QC,
    Reason,
    Region,
    ReturnReason,
    ShipAddress,
    ShipmentId as TableID,
    ShipmentLines,
    ShippingAddressId,
    ShippingLane,
    ShippingMethod,
    Stage,
    Status,
    StUQty,
    Supervisor,
    Warehouse,
    Weight;
SQL SELECT *
FROM snap.shipment
Where Stage = 90;

Concatenate(Snap)

Load
    'RECEIPT' as Table,
    ActualWeight,
    BizId,
    BizPurchaseOrder,
    BizSupplierId,
    CarouselStockConsignments,
    CarrierId,
    CarrierTrackingNumber,
    ConsignmentId,
    DateArrival,
    DateClosed,
    DateCollected,
    DateCreated,
    Month(DateCreated) as Month,
    Week(DateCreated) as Week,
    Year(DateCreated) as Year,
    Date(DateCreated) as Date,
    DateDueIn,
    DateReceipt,
    DateSuspended,
    InvoiceAddressId,
    LineQty,
    `Lines`,
    LoadId,
    LoadSequence,
    MaintInd,
    ManifestNumber,
    OrderClass,
    OrderType,
    OverdueInd,
    OwnerId as Client,
    PaperworkInd,
    POC,
    Prime,
    Priority,
    PurchaseOrder,
    QA,
    Reason,
    ReceiptAddresses,
    ReceiptId as TableID,
    ReceiptLane,
    ReceiptLines,
    Region,
    ReturnReason,
    Route,
    ShippingAddressId,
    ShippingMethod,
    Site,
    Stage,
    Status,
    StockStatus,
    StUQty,
    Supervisor,
    SupplierGroup,
    SupplierId,
    SupplierName,
    SupplierRef,
    Warehouse,
    Weight,
    xdocid;
SQL SELECT *
FROM snap.receipt
Where Stage = 90;

Concatenate(Snap)

Load
    'PACK' as Table,
    cslsupplier,
    cslsupplierref,
    easyWebref,
    `edit_text`,
    editflag,
    `export_flag`,
    `export_stamp`,
    Month(`lock_stamp`) as Month,
    Week(`lock_stamp`) as Week,
    Year(`lock_stamp`) as Year,
    Date(`lock_stamp`) as Date,
    id,
    `inserted_date`,
    `label_data`,
    `lock_flag`,
    `lock_stamp`,
    `order_ref`,
    `processed_flag`,
    `processed_stamp`,
    `qc_flag`,
    `qc_reason`,
    `qc_stamp`,
    `qc_user`,
    `snap_ref` as TableID,
    stu,
    xdocid,
    `xml_data`;   
SQL Select *
FROM snap.shipmentlog;

Concatenate(Snap)

Load
    'TASK' as Table,
    Assignment as TableID,
    Class,
    DateAssigned,
    DateClosed,
    DateCreated,
    Month(DateAssigned) as Month,
    Week(DateAssigned) as Week,
    Year(DateAssigned) as Year,
    Date(DateAssigned) as Date,
    DateReleased,
    Device,
    FromBay,
    FromZone,
    Job,
    Operator,
    Overdue,
    Owner,
    Quantity,
    Revenue,
    ServiceTime,
    Stage,
    STT,
    SuspensionTime,
    TaskID,
    TaskType,
    ToBay,
    ToZone,
    WorkTime,
    WorkTimeAverage;

SQL Select *
FROM snap.task;

The lookups I need are in the "TASK" and "PACK" tables - i think if you can give me one example i should be able to replicate it across the others.

Take for example - the "Snap_ref" field in the "PACK" table is the same as the "ShipmentId" field in the "SHIPMENT" table - i want to be able to use this reference to fill in the "Lines", "LineQty" and "Client" fields