topic Concatenation and Lookups in QlikView

Concatenation and Lookups

Thu, 18 Sep 2014 09:54:34 GMT

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

Re: Concatenation and Lookups

jagan — Thu, 18 Sep 2014 10:00:24 GMT

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

ashfaq_haseeb — Thu, 18 Sep 2014 10:01:07 GMT

You need to join table 1 and table 3.

Then concatenate this result with table 2.

Regards

ASHFAQ

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 10:07:55 GMT

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;

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:12:34 GMT

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?

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:15:12 GMT

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

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 10:19:32 GMT

Did you check my script below without join

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:21:55 GMT

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....

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 10:25:39 GMT

Yes

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:32:34 GMT

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

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:33:40 GMT

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

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 10:52:32 GMT

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

Re: Concatenation and Lookups

Thu, 18 Sep 2014 10:55:58 GMT

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

Re: Concatenation and Lookups

Anonymous — Thu, 18 Sep 2014 10:58:08 GMT

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;

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 11:08:11 GMT

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 )?

Re: Concatenation and Lookups

Thu, 18 Sep 2014 11:13:10 GMT

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

Re: Concatenation and Lookups

anbu1984 — Thu, 18 Sep 2014 11:53:39 GMT

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;

Re: Concatenation and Lookups

Fri, 19 Sep 2014 11:54:06 GMT

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