Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Concatenation and Lookups

I have 3 tables which I have concatenated.

Table NameShipmentIdAmount
Table 11£10
Table 12£20
Table 13£30

Table NameReceiptIdAmount
Table 21£10
Table 22£20
Table 23£30

Table NameShipment idTime
Table 3107:00
Table 3208:00
Table 3309:00

The table currently looks like this:

Table NameShipmentIdReceiptIDAmountTime
Table 11-£10
Table 12-£20
Table 13-£30
Table 2-1£10
Table 2-2£20
Table 2-3£30
Table 31--07:00
Table 32--08:00
Table 33--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 NameShipmentIdReceiptIDAmountTime
Table 11-£10
Table 12-£20
Table 13-£30
Table 2-1£10
Table 2-2£20
Table 2-3£30
Table 31-£1007:00
Table 32-£2008:00
Table 33-£3009:00

Can someone help me apply this lookup?

Thanks

17 Replies
MVP & Luminary
MVP & Luminary

Re: Concatenation and Lookups

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.

Re: Concatenation and Lookups

You need to join table 1 and table 3.

Then concatenate this result with table 2.

Regards

ASHFAQ

anbu1984
Honored Contributor III

Re: Concatenation and Lookups

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;

Not applicable

Re: Concatenation and Lookups

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?


Not applicable

Re: Concatenation and Lookups

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

anbu1984
Honored Contributor III

Re: Concatenation and Lookups

Did you check my script below without join

Not applicable

Re: Concatenation and Lookups

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
Honored Contributor III

Re: Concatenation and Lookups

Yes

Not applicable

Re: Concatenation and Lookups

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,
    R
egion,
   
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