Two fields, same table, same data

[ciaran_mcgowan]

Hi,

I have a table containing Delivery information and another that contains Warehouse information (like below).

Delivery:

TripNo	PickupLocation	DeliveryLocation	Date
001	South Warehouse	Shop 42	02/04/2014
002	Shop 42	South Warehouse	03/04/2014

Warehouse:

WarehouseNumber	WarehouseName
03	South Warehouse
04	East Warehouse

Each truck brings home a return collection from the shop it delivers to so there are always two rows created for each trip. I had linked the two tables using the warehouse name like this:

Delivery:          LOAD PickupLocation AS Warehouse

Warehouse:     LOAD WarehouseName AS Warehouse

But if I select 'South Warehouse' link from my list box, it will only show Warehouses that deliveries were pickup up from (ie. Trip 001). What is the best way to accomplish selecting Warehouse from the list box and having it display both PickupLocation & DeliveryLocation (ie. Trip 001 & Trip 002)?

Thanks

[MK_QSL · Accepted solution]

Test:

Load

  RecNo() as NO,

  TripNo,

  PickupLocation,

  DeliveryLocation,

  Date

Inline

[

  TripNo, PickupLocation, DeliveryLocation, Date

  001, South Warehouse, Shop 42, 02/04/2014

  002, Shop 42, South Warehouse, 03/04/2014

];

Warehoues:

Load

  NO,

  PickupLocation as Warehouse

Resident Test;

Load

  NO,

  DeliveryLocation as Warehouse

Resident Test;

Load

  WarehouseNumber,

  WarehouseName as Warehouse

Inline

[

  WarehouseNumber, WarehouseName

  03, South Warehouse

  04, East Warehouse

];

=====================================

[its_anandrjs]

You can load like

Delivery:

LOAD * Inline

[

TripNo, PickupLocation, DeliveryLocation, Date

001, South Warehouse, Shop 42, 02/04/2014

002, Shop 42, South Warehouse, 03/04/2014

];

T:

LOAD RowNo() as Rid, TripNo, PickupLocation as WarehouseName,Date

Resident Delivery;

Concatenate

LOAD RowNo() as Rid,TripNo, DeliveryLocation as WarehouseName,Date

Resident Delivery;

DROP Table  Delivery;

Warehouse:

load WarehouseNumber, WarehouseName;

LOAD * Inline

[

WarehouseNumber, WarehouseName

03, South Warehouse

04, East Warehouse

];

Hope this helps

[maxgro]

see attachment

[ciaran_mcgowan]

Hi Manish,

I'm not sure if an inline load is feasible here. The examples above are greatly simplified: I've 21 years worth of deliveries, with 45 Warehouses, with around 316 deliveries per year (158 pickups & 158 deliveries).

Do you know of another way? Thanks.

[ciaran_mcgowan]

Hi Anand,

Same response as to Manish (above),

I'm not sure if an inline load is feasible here. The examples above are greatly simplified: I've 21 years worth of deliveries, with 45 Warehouses, with around 316 deliveries per year (158 pickups & 158 deliveries).

Do you know of another way? Thanks.

[ciaran_mcgowan]

Hi Massimo,

Same response as to Manish & Anand (above),

I'm not sure if an inline load is feasible here. The examples above are greatly simplified: I've 21 years worth of deliveries, with 45 Warehouses, with around 316 deliveries per year (158 pickups & 158 deliveries).

Do you know of another way? Thanks.

[MK_QSL]

Inline load is here to use your sample data.

You can definitely use your Direct Database (i.e. Oracle or SQL Server), but believe me, the best solution is to get your requirements using script level work.

At UI level, looking upon your database, will severally affect performance.

[maxgro]

we used (here and in other post) inline load because often is the only way to try before answering

why? because you have the data (qvd, database, etc, .....); we don't

replace  inline load of my (or other answers)

Delivery:

LOAD * Inline

[

TripNo, PickupLocation, DeliveryLocation, Date

001, South Warehouse, Shop 42, 02/04/2014

002, Shop 42, South Warehouse, 03/04/2014

];

with your data (this can't work on my pc, so I used inline)

Delivery:

LOAD

TripNo, PickupLocation, DeliveryLocation, Date

from

yourfile.qvd (qvd);

[MarcoWedel]

Hi Ciaran,

another solution:

Warehouse:
LOAD *
FROM [http://community.qlik.com/thread/113097]
(html, codepage is 1252, embedded labels, table is @2);

Delivery:
LOAD *
FROM [http://community.qlik.com/thread/113097]
(html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD Distinct
  PickupLocation,
  DeliveryLocation,
  AutoNumberHash128(PickupLocation, DeliveryLocation) as %LocKey
Resident Delivery;

tabLinkWarehouse:
LOAD Distinct
  PickupLocation as WarehouseName,
  %LocKey
Resident Delivery;

LOAD Distinct
  DeliveryLocation as WarehouseName,
  %LocKey
Resident Delivery;

regards

Marco