topic Joining Tables Together in QlikView

Joining Tables Together

Tue, 11 Aug 2015 16:16:33 GMT

Hello everyone,

I have issues to join fields from 3 different tables together.

My first table is called Main Data , the second is called Movements and the last one is Receipts.

What i want is to bind Main Data to movements with the fields : - Request -Batch

And to bind Receipts to Movements with : -Skid -Batch

Is it possible to bind those 3 tables together, so that if I select and entry of my Receipt table for example qlikview returns me data from my Movements table thanks to the link ?
I have joined the Load Statement of each table so that you can figure out the links.
Thank you everyone

Alexandre BERNARD

Tables :

[Main Data]:

LOAD

Request,

Part,

[Created Date]

[Created Time]

[Issued Date]

[Issued Time]

[Shipped Date]

[Shipped Time]

Manifests,

POU,

[Req Qty],

[Issue Qty],

[Req Qty/Vendor],

Status,

[Job Refs],

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

FROM

ALEX\statusanalysis.xlsx

(ooxml, embedded labels);

[Movements]:

LOAD

[Supp-Code],

[Prod-Code],

[Prod-Desc],

[Alt-Prod],

[2nd-Alt-Part],

Type,

Date,

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],

Skid,

[Ship-Date],

MoveKey,

Dims,

Hawb,

Mawb,

[Receipt-User],

[Rct-Date],

[Rct-Inv],

Loc,

IssQty,

[RPO-Ref]

FROM

ALEX\Original\MOVES.xls

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

[Receipts]:

LOAD

BookRef,

Batch,

Skid,

Cartons as ReceiptCartons,

PackType,

[PR Logname],

[DT Logname]

FROM

ALEX\ReceiptTimes.xls

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

Remove(Row, Pos(Top, 1))

));

Re: Joining Tables Together

maximiliano_vel — Tue, 11 Aug 2015 16:30:27 GMT

Pls try This

[Main Data]:

LOAD Request & Batch as MainMovements.Key,

Request as MainData.Request,

Part,

[Created Date]

[Created Time]

[Issued Date]

[Issued Time]

[Shipped Date]

[Shipped Time]

Manifests,

POU,

[Req Qty],

[Issue Qty],

[Req Qty/Vendor],

Status,

[Job Refs],

Batch as MainData.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

FROM

ALEX\statusanalysis.xlsx

(ooxml, embedded labels);

[Movements]:

LOAD Request & Batch as MainMovements.Key,

Batch & Skid as MovementsReceipts.Key,

[Supp-Code],

[Prod-Code],

[Prod-Desc],

[Alt-Prod],

[2nd-Alt-Part],

Type,

Date,

Request as Movements.Request,

[Good-Qty],

[Quar-Qty],

[Quar-Ind],

[Cancel-Ind],

[Good-Bal],

[Quar-Bal],

Batch as Movements.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],

Skid as Movements.Skid,

[Ship-Date],

MoveKey,

Dims,

Hawb,

Mawb,

[Receipt-User],

[Rct-Date],

[Rct-Inv],

Loc,

IssQty,

[RPO-Ref]

FROM

ALEX\Original\MOVES.xls

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

[Receipts]:

LOAD Batch & Skid as MovementsReceipts.Key,

BookRef,

Batch as Receipts.Batch,

Skid as Receipts.Skid,

Cartons as ReceiptCartons,

PackType,

[PR Logname],

[DT Logname]

FROM

ALEX\ReceiptTimes.xls

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

Remove(Row, Pos(Top, 1))

));

Re: Joining Tables Together

Anonymous — Tue, 11 Aug 2015 16:37:16 GMT

Hi Alexandre,

Please try below code,resolve if any synthetic keys:

Tables :

[Main Data]:

LOAD

Request & Batch as RequestBatch,

Request as MainRequest,

Part,

[Created Date],

[Created Time],

[Issued Date],

[Issued Time],

[Shipped Date],

[Shipped Time],

Manifests,

POU,

[Req Qty],

[Issue Qty],

[Req Qty/Vendor],

Status,

[Job Refs],

Batch as MainBatch,

[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

FROM

ALEX\statusanalysis.xlsx

(ooxml, embedded labels);

[Movements]:

LOAD

Request & Batch as RequestBatch,

Skid & Batch as SkidBatch,

[Supp-Code],

[Prod-Code],

[Prod-Desc],

[Alt-Prod],

[2nd-Alt-Part],

Type,

Date,

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],

Skid,

[Ship-Date],

MoveKey,

Dims,

Hawb,

Mawb,

[Receipt-User],

[Rct-Date],

[Rct-Inv],

Loc,

IssQty,

[RPO-Ref]

FROM

ALEX\Original\MOVES.xls

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

[Receipts]:

LOAD

Skid & Batch as SkidBatch,

BookRef,

Batch as ReceiptBatch,

Skid as SkidBatch,

Cartons as ReceiptCartons,

PackType,

[PR Logname],

[DT Logname]

FROM

ALEX\ReceiptTimes.xls

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

Remove(Row, Pos(Top, 1))

));

Regards

Neeth