Joining Tables

Tue, 11 Aug 2015 18:51:39 GMT

Hello Everyone I have an issue to bind 3 tables together. My following tables are :

-Main Data

- Movements

- Receipts

The main data table shares 2 fields with the Movements table and the Receipts table shares 2 fields with the Movement table.

The problem is that one field (Batch number) is present in each of the 3 tables but the issues batch numbers are in the main data table whereas the receipt batch number are in the table Receipt. The movement table contains all the batch numbers.

I want to link my table with those fields ;
Main Data & Movements : Batch + Request

Receipts & Movements : Batch + Skid

Here are the following LOAD Statements corresponding to my 3 Tables, Thanks to anyone that could help me ;

[Main Data]:

LOAD Request,

Part,

[Created Date]as CreatedDate,

[Created Time] as CreatedTime,

[Issued Date] as IssuedDate,

[Issued Time]as IssuedTime,

[Shipped Date]as ShippedDate,

[Shipped Time] as ShippedTime,

Manifests,

POU,

[Req Qty],

[Issue Qty],

[Req Qty/Vendor],

Status,

[Job Refs],

Batches as Batch,

[Pick Time],

[Travel ID],

[Num-Ctns]as Cartons,

[GRN Date] as ArrivalDate,

[GRN Time] as ArrivalTime,

[GRN PO/LINE],

[GRN comment],

[Zero Picks],

Warehouse,

IssueQty,

Vendor,

Day([Created Date]) as Day,

Week([Created Date]) as Week,

Month([Created Date]) as Month

FROM

ALEX\statusanalysis.xlsx

(ooxml, embedded labels);

[Movements]:

LOAD [Supp-Code],

[Prod-Code],

[Prod-Desc],

[Alt-Prod],

[2nd-Alt-Part],

Type,

Date,

[Manu-Ref] as Request,

[Good-Qty],

[Quar-Qty],

[Quar-Ind],

[Cancel-Ind],

[Good-Bal],

[Quar-Bal],

Batch,

[Job-Ref],

[Shippers-Ref],

Consignee,

Consignor,

[POD-Date],

Signature,

[Rct-Manu-Refs],

[Rct-Ctnr-Refs],

Cartons,

[Tran-Type],

[PO-Num],

[Item-Num],

[Batch-Time],

Comment,

[Serial-Refs],

[Cstm-Ref],

[Pallet-Ref] as Skid,

[Ship-Date],

MoveKey,

Dims,

Hawb,

Mawb,

[Receipt-User],

[Rct-Date],

[Rct-Inv],

Loc,

IssQty,

[RPO-Ref],

Day([Date]) as Day,

Week([Date]) as Week,

Month([Date]) as Month

FROM

ALEX\Original\MOVES.xls

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[Receipt]:

LOAD BookRef,

Batch,

Skid,

Cartons as ReceiptCartons,

PackType,

[PR Logname],

[DT Logname],

Day([PR Start Date]) as Day,

Week([PR Start Date]) as Week,

Month([PR Start Date]) as Month

FROM

ALEX\ReceiptTimes.xls

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, filters(

Remove(Row, Pos(Top, 1))

));

Re: Joining Tables

maximiliano_vel — Tue, 11 Aug 2015 18:57:20 GMT

Didn't you post this already??

Joining Tables Together

Re: Joining Tables

ogster1974 — Tue, 11 Aug 2015 19:05:08 GMT

‌create composite keys to handle the joins e.g

IN receipt table create a receipt key something like

BAtch + skid as %receiptkey

ANd create the same key in movement. You can do this on load and Qlik will handle the rest. Remove duplicate columns only have columns in the one table to avoid synthetic keys.

Re: Joining Tables

Tue, 11 Aug 2015 19:06:33 GMT

I did but the LOAD Statement were not correct.

I have removed the previous discussion

Re: Joining Tables

Anonymous — Tue, 11 Aug 2015 19:45:45 GMT

Create the keys of Batch and Skid in Receipt table as well as Movement table & Batch and Request in main table as well as movement table...

These three tables will then join on the basis of these two keys.

topic Re: Joining Tables in QlikView

Joining Tables

Re: Joining Tables

Re: Joining Tables

Re: Joining Tables

Re: Joining Tables