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

[#2]

only complication may be that in the "Task" table the assignment field might match the "receiptId" field from the Receipt table of the "shipmentId" field from the Shipment table

[anbu1984]

Can you give an another example based on sql? So you dont need lookup for Receipt table?

[#4]

I'm not sure I understand what you mean by "can you give another example based on sql?"

The receipt table doesn't need any lookups on it but the Task table would need to lookup against it using the receiptId column and the "lines, LineQty and OwnerId" fields

[Anonymous]

Hi Drew,

Try below script-

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

Result:

Load *,

If( Not(IsNull(ShipmentId)) And ShipmentId = Previous(ShipmentId),Peek(Amount),Amount) as DesiredAmount

Resident Initial

Order by ShipmentId,Amount desc;

Drop table Initial;

[anbu1984]

Lets take Lines field. In which tables Lines field have to be filled? Which is lookup table to refer? Lookup columns (Like Pack.Snap_ref = Shipment.ShipmentId )?

[#7]

The "PACK" table will need to be populated with "Lines"

The table it will look up against is "SHIPMENT"

The common data is "shipmentId" (SHIPMENT) and "Snap_ref" (PACK)

Pack.snap_ref = shipment.shipmentid

Task.assignment = shipment.shipmentid OR receipt.receiptid

[anbu1984]

Try this

CONNECT32 TO dbserver;

Snap:

Load

    'SHIPMENT' as Table,

    BizId,

...........

    WorkTime,

    WorkTimeAverage;

SQL Select *

FROM snap.task;

NoConcatenate

Final:

Load *, If( Not(IsNull(TableID)) And TableID = Previous(TableID),Previous(Lines),Lines) Resident Snap Order by ShipmentId,Lines desc;

[#9]

I eventually got a work around using a series of applymap() formulas