Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
You need to join table 1 and table 3.
Then concatenate this result with table 2.
Regards
ASHFAQ
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;
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?
lookup('AMOUNT','ShipmentId','ShipmentId,'TABLE1') AS AMOUNT
Did you check my script below without join
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....
Yes
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